How to search A1:M31 (each column is a month) for bills named “Avista” and SUM the cell amount beneath it each time it’s found?
Hallo
Maybe?
=SUM( IF( $A$1:$M$31="Avista" ; $A$2:$M$32 ; "" ))
close it with ctrl+shift+enter
btw. thats IMHO a bad designed bookkeeping!
2 Likes
Yeah, I may be a rookie, but I love puzzles. That didn’t work as it came up with #Value! It’s a checking account - I’ll look for a better design.
IMHO, at 70 I find it difficult to pick up all the new acronyms 
I’d guess imho has been around for over thirty years, at least in my humble opinion. Albeit I am your senior by quite a few years, I don’t seem to have the same problem?
2 Likes
Click in the cell with @karolus formula, delete the last parenthesis and type it in again then press Ctrl+Shift+Enter to create the array formula.
In extensions search by account and there are some suggestions Extensions » Extensions
For us Boomers there is https://www.webopedia.com/definitions/text-abbreviations/ but I bet most people wouldn’t know most of them
- Why exactly 13 months?
- The “column-per-month-design” (or per year or per day or sheet-per-month) is very doubtable. Many beginners do it this way just because they are accustomed to the ways they did it on paper. Professionals would (most likely create a database or, if creating a spreadsheet) simply use one row per relation (bill). They then can (e.g.) sum using SUMIF() or SUMIFS() lots of selected subsets of the bill amounts as needed for a specific task.
- If you think you need to sum values below a selecting string, this seems to signal that you use more than one row for a single bill. Very bad idea which would cause trouble again and again.
Karolus’ suggestion works for me, with a dataset I created for the purpose. Your dataset (table) content may differ in invisible ways. If you upload a copy of your file (with sensitive info removed ; remember this is a public place), it is easier for the helpers to suggest a sensible solution.
The #VALUE message indicates that you failed to create the array formula. Make sure you insert it as an array formula by holding down ctrl+shift when you enter it. This makes the formula appear inside curly braces after entry, indicating a successful array formula entry. A different key combo may be required if you are using a Mac.
Another way is to use the SUMIF() fuinction (to the extent required, array context is implied with this function, so you just enter it normally):
=SUMIF( $A$1:$M$31;“Avista” ; $A$2:$M$32 )`
Note that Karolus’ suggestion is case sensitive and will only match the exact string “Avista”, while the SUMIF is not case sensitive and will also match “AVISTA” and “avista”.
Note also that if you have leading or trailing spaces in your “tag cells”, they will not match the given formulas.
WOW! I learned something new I used Karolus’ =SUM(IF…; left justified all my cells and it worked! Thank you also Keme1 for the ‘tag’ helpful hints. Where do I hit “solved” on this thing.