Multi condition sum in excel

In our daily work in excel, there are situations,  which requires summing of range based on multiple conditions. In this scenario, multiconditional sum  forumula of excel comes useful. Starting Excel 2007, we have a direct excel formula for multi conditional sum. The excel versions prior to 2007, we can use sumproduct excel function.
SUMIFS is the multiconditional formula in Excel. For single criteria sum, we can use SUMIF function. Syntax of SUMIFS and SUMIF are slighlty different. Learning these two functions, will help to work efficiently with Excel.

Recommended Reading:

How to use SUMIF function in Excel

Multi Condition sum in Excel 2007, Excel 2010 and Later

Syntax of multi condition formula Sumifs is
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
Examples for multi condition sum:
sum the numbers in the range A1:A20 only if the corresponding numbers in B1:B20 are greater than zero (0) and the corresponding numbers in C1:C20 are less than 10.
Formula is: =SUMIFS(A1:A20, B1:B20, “>0”, C1:C20, “<10”)

Multi Condition sum in Excel 2003 or older

The alternate function for SUMIFS is sumproduct function in Excel. It does summing of ranges, based multiple sets of conditions. It is not as simple as SUMIFS. But once practiced and learned the logic, very useful.
Syntax of multicondition formula sumproduct:
=sumproduct((Criteria1)*(Criteria2)………..(CriterialN), Sumrange)
Note: For using sumproduct as multicondition sum, the ranges neeeds to be even.
Let us take above example with sumproduct:
=sumproduct((B1:B20>0)*(C1:C20<10)*(A1:A20))

Multi condition sum using array function: Using array function for multicondition sum is not recommended as that takes more time and the formula is tedious and it requires every time to Shift+Ctrl+Enter. So i omitted array function here.

This Excel tips and tricks I tried to cover the mutli conditional sum in Excel. For your reference, below are the few other useful functions of excel.

  1. COUNTIF — COUNTF excel function is useful for single criterion count.
  2. COUNTIFS- This excel function is useful for multi conditional count.

There are many other count formulas in Excel. These are very commonly used functions of Excel.

Meet the Author

Hi, I am Aniruddha, an avid blogger since 2008 who likes writing about new gadgets, smartphones and excel. You can find them at www.tech2touch.com. You can also follow me on Twitter @tech2touch or my Facebook page tech2touch. You can subscribe to my YouTube channel tech2touch. Enjoy reading and watching!!!

Shares