Multi-Criteria SUMIF

Home/Arrays/Multi-Criteria SUMIF

Multi-criteria SUMIF

I use SUMIF to sum a range based on one condition. Is there a way to sum a range based on two or more conditions?

By Neale Blackwood

One way is to use SUMPRODUCT. The following formula will sum the range C1:C10 based on the contents of A1:A10 and B1:B10. Cell E1 contains the condition for column A and cell F1 contains the condition for column B.

=SUMPRODUCT((A1:A10=E1)*(B1:B10=F1)*(C1:C10))

You can also use an array formula. Another option is to insert a column in the data and combine two or more columns together by using the & (see other question). [see the Extras below] Then use the new column in the SUMIF.

A word of warning on these three solutions. Your calculation time will increase if you are using large numbers of SUMIF, SUMPRODUCT or Array formulae.

Another alternative could be to use a pivot table to summarise the data. This increases the file size rather than calculation time. Remember, a pivot table needs to be refreshed if the data changes, either manually or via macro.

Excel 2007+ SUMIFS

Excel 2007 introduced the SUMIFS formula that allows you to so multiple criteria SUMIF calculations (up to 127 criteria!) without resorting to the more complicated SUMPRODUCT function.

Syntax

=SUMIFS(sum_range, range1, criteria1, range2,criteria2)

In the example above the formula would be:

=SUMIFS(C1:C10,A1:A10,E1,B1:B10,F1)

2017-03-08T03:53:56+00:00 Arrays|