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)
8 Replies
SUMIFS does not work here because the sum range is two-dimensional, while one criterion range is vertical and the other is horizontal. Their sizes do not match.
In Microsoft 365, try:
=SUMPRODUCT( ('Query Budget'!$A$10:$A$1000=F$1)* ('Query Budget'!$C$5:$HV$5=$D5)* 'Query Budget'!$C$10:$HV$1000 )
This formula:
- Finds rows where column A equals F1
- Finds columns whose row 5 heading equals D5
- Sums the values where both conditions are met
Notice that the column-header range starts at C5, not A5, so it lines up exactly with the data range C10:HV1000.
If the workbook has a very large data set, avoid using entire-column references with SUMPRODUCT, as that can slow calculation considerably.
To sum data across multiple rows and columns based on a specific criteria, a standard SUMIFS will usually fail or return an error because it expects a single-column or single-row sum range.
The most efficient and reliable way to handle this without changing your data structure is by using the SUMPRODUCT function. It evaluates arrays natively and allows multi-dimensional math.
You can try this formula format:
=SUMPRODUCT((A2:A10="YourCriteria")*(B2:D10))
How this works:
- (A2:A10="YourCriteria") checks your criteria column and creates an array of TRUE (1) and FALSE (0) values.
- *(B2:D10) multiplies that column array against the entire multi-column numeric data matrix.
- Finally, SUMPRODUCT adds up all the calculated products, giving you the exact sum across all rows and columns.
Alternatively, if you are using modern Office 365, you can also wrap a SUM around a FILTER function like this:
=SUM(FILTER(B2:D10, A2:A10="YourCriteria", 0))
Give this a try, and it should resolve the issue you are facing with your dataset!
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.
Hi MirsadaMK ,
In light of the answers given so far, I'm struggling to actually understand your question. Could you show a screenshot of part of your table if you can't show it all?
Or even ask AI to generate sample data that is anonymised and show that.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.
