Forum Discussion
Sum of data across multiple columns & rows based on Criteria
I have a data set that ranges across multiple rows and columns. I am building a formula and looking to sum based on multiple criteria. I tried sumifs, however, that does not work. Below is my example using a hypothetical sumifs (keeping in mind, this does not work). Any suggestions on how to return my expected result would be greatly appreciated.
=SUMIFS('Query Budget'!$C$10:$HV$1000,'Query Budget'!$A$10:$A$1000,F$1,'Query Budget'!$A$5:$HV$5,$D5)
5 Replies
Maybe this will explain it a bit better. I have data across multiple rows and columns. Top row has unique criteria (codes/headers, if you will, in numerical format) across all columns. First column has numerical codes that may repeat in several rows. I need the formula to look at all columns and rows, find each instance of a specific code combination, and add up those cells. For example, look for 123 in the top row, and all instances of 567 in the first column, and add all the cells where those intersect.
Yep that is exactly what I guessed and that is exactly what that formula will do. Here is a sample sheet I created with a/b headers and 1/2 on the left column then the formula looks at cells J1 and K1 to decide which combination to sum...
and just to show it really does work here I copied the formula and did all 4 combinations of a/b and 1/2 and then compared the sum of those 4 combinations with the sum of the whole:
both = 2016
So the concept works, the question is why are you saying it doesn't work? If you are getting an error what is the error? Are all the values in the central grid actual values? (i.e. no text or error values) Are you sure you lined up the rows and columns? The original example had A5:HV5 for the header row but the data was only in columns C10:HV1000 so those columns didn't line up (hence in the prior answer I assumed you ment C5:HV5.
The problem is that the ranges aren't the same shape and don't even line up in some cases. That said if you want conditionals on both horizontal and vertical I would suggest you could try a more direct product for your conditionals. Traditionally we would use SUMPRODUCT but probably don't really need that now, but maybe something like:
=SUM('Query Budget'!$C$10:$HV$1000 * ('Query Budget'!$A$10:$A$1000=F$1)*('Query Budget'!$C$5:$HV$5=$D5))
Unfortunately, that didn't work. Thank you, though.
"that didn't work"? did you get an error or just not get the answer you wanted? considering you didn't give us much to go on but a formula that "doesn't work" I really wasn't necessarily expecting (but hoping) the exact formula I gave you would give you the exact answer you wanted but that the concept and the format was a way for you to do what I think you want to do.
That said, I do notice you have absolute references for nearly all except for the F$1 and $D5, are you expecting them to be relative / change with each value in some way? If so that won't happen/work and you have to specify the actual array to use (and the dimensions must match.
If you can specify what didn't work and maybe more details on what you are trying to do then maybe I can get you to the finish line.
