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...
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.