SUMIF Multiple Columns with Multiple Criteria

Question

I am trying to use a SUMIF formula to sum information in a table format in Excel. I need to expand the sum range from 1 column to 3 columns.

Original Formula based 1 column:

=SUMIFS([[C1]:[C1]],[[Name]:[Name]],"ABC",[[Program]:[Program]],1)

Example:

| Name | Program | C1 | C2 | C3 |
| ---- | ------- | -- | -- | -- |
|ABC|1|10|15|0|
|DEF|2|20|0|3|
|ABC|3|5|9|8|
|DEF|2|20|0|3|
|GHI|3|7|4|8|

I've tried to update the formula as following:

=SUMIFS([[C1]:[C3]],[[Name]:[Name]],"ABC",[[Program]:[Program]],1)

Expecting to see the following results:

|Name|Program|Sum|
|-|-|-|
|ABC|1|25|
|ABC|3|22|
|DEF|2|23|
|GHI|3|19|

However, I am getting a #VALUE as the result when I expanded the sum to 3 columns.

I have thought about using SUMPRODUCT, but my table has over 1,000 rows of data. SUMPRODUCT appears limited to an array of 255.

Ron Rosenfeld Ron Rosenfeld · Accepted Answer · 2021-06-08T18:39:39

You can't use SUMIFS since the criteria range and sum range need to be the same dimensions.

If you have O365 you can use FILTER

I don't understand your objection to SUMPRODUCT. But, of course, you did not share the formula you found to malfunction, so it is hard to say what you did wrong.

But depending you your version of Excel, you might be able to use simply:

=SUM((Table1[Name]=H10)*(Table1[Program]=I10)*Table1[[C1]:[C3]])

entered either normally or as an array formula with ctrl+shift+enter

I'd suggest you retry SUMPRODUCT because I am not aware of any 255 row limitation, and have not seen it.

SUMIF Multiple Columns with Multiple Criteria

1 Answers