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.
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.
=SUMIFS(sum_range, range1, criteria1, range2,criteria2)
In the example above the formula would be: