Using SUMIFS with multiple AND OR conditions

excel sum conditional sumifs
Dominic picture Dominic · Nov 27, 2013 · Viewed 131k times · Source

I would like to create a succinct Excel formula that SUMS a column based on a set of AND conditions, plus a set of OR conditions.

My Excel table contains the following data and I used defined names for the columns.

  • Quote_Value (Worksheet!$A:$A) holds an accounting value.
  • Days_To_Close (Worksheet!$B:$B) contains a formula that results in a number.
  • Salesman (Worksheet!$C:$C) contains text and is a name.
  • Quote_Month (Worksheet!$D:$D) contains a formula (=TEXT(Worksheet!$E:$E,"mmm-yy"))to convert a date/time number from another column into a text based month reference.

I want to SUM Quote_Value if Salesman equals JBloggs and Days_To_Close is equal to or less than 90 and Quote_Month is equal to one of the following (Oct-13, Nov-13, or Dec-13).

At the moment, I've got this to work but it includes a lot of repetition, which I don't think I need.

=SUM(SUMIFS(Quote_Value,Salesman,"=JBloggs",Days_To_Close,"<=90",Quote_Month,"=Oct-13")+SUMIFS(Quote_Value,Salesman,"=JBloggs",Days_To_Close,"<=90",Quote_Month,"=Nov-13")+SUMIFS(Quote_Value,Salesman,"=JBloggs",Days_To_Close,"<=90",Quote_Month,"=Dec-13"))

What I'd like to do is something more like the following but I can't work out the correct syntax:

=SUMIFS(Quote_Value,Salesman,"=JBloggs",Days_To_Close,"<=90",Quote_Month,OR(Quote_Month="Oct-13",Quote_Month="Nov-13",Quote_Month="Dec-13"))

That formula doesn't error, it just returns a 0 value. Yet if I manually examine the data, that's not correct. I even tried using TRIM(Quote_Month) to make sure that spaces hadn't crept into the data but the fact that my extended SUM formula works indicates that the data is OK and that it's a syntax issue. Can anybody steer me in the right direction?

Answer

barry houdini picture barry houdini · Nov 27, 2013

You can use SUMIFS like this

=SUM(SUMIFS(Quote_Value,Salesman,"JBloggs",Days_To_Close,"<=90",Quote_Month,{"Oct-13","Nov-13","Dec-13"}))

The SUMIFS function will return an "array" of 3 values (one total each for "Oct-13", "Nov-13" and "Dec-13"), so you need SUM to sum that array and give you the final result.

Be careful with this syntax, you can only have at most two criteria within the formula with "OR" conditions...and if there are two then in one you must separate the criteria with commas, in the other with semi-colons.

If you need more you might use SUMPRODUCT with MATCH, e.g. in your case

=SUMPRODUCT(Quote_Value,(Salesman="JBloggs")*(Days_To_Close<=90)*ISNUMBER(MATCH(Quote_Month,{"Oct-13","Nov-13","Dec-13"},0)))

In that version you can add any number of "OR" criteria using ISNUMBER/MATCH

Related questions

Sum function in VBA

I have a problem with summing cells in vba. I need to use Cells(a,b): Range("A1").function="=SUM(Range(Cells(2,1),Cells(3,2)))" but it doesn't work.

Read More
How to SUM parts of a column which have same text value in different column in the same row

I have a column with names and a column with numbers: FirstName Name Number John Smith 17 John Smith 26 Peter Smith 116 Peter Smith 25 Franck Black 17 Luke Peterson 17 Luke Peterson 37 Names with same FirstName and Name represent the same person. I need …

Read More
SUM the result of dividing two cells (SUMPRODUCT but division instead)

I'm looking to do the equivalent of SUMPRODUCT but with division. Is there a way to add the results from dividing two arrays? Example: Column A has the "life" of an asset in years (10, 20, 10). Column B has the Value of …

Read More