Using SUMIFS with multiple AND OR clauses

Question

I would like to create a concise Excel formula in which SUMS is 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 specific names for the columns.

• Quote_Value (Worksheet! $ A: $ A) contains the 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 the formula (= TEXT (Worksheet! $ E: $ E, "mmm-yy")) to convert the date / time from another column to a text month link.

I want to use SUM Quote_Value if Salesman is equal to JBloggs and Days_To_Close is equal to or less than 90, and Quote_Month is equal to one of the following (October-13, November-13 or December-13).

At the moment I have this to work, but it includes many repetitions that I do not 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 would like to do is something more than the following, but I cannot 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")) 

This formula is not an error, it simply returns 0. But if I manually check the data, it is not. I even tried using TRIM (Quote_Month) to make sure that the spaces did not fill in the data, but the fact that my extended SUM formula works indicates that the data is ok and that this is a syntax issue. Can someone direct me in the right direction?

Answer 1

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 will return an “array” of 3 values ​​(one for each of “Oct-13”, “November-13” and “Dec-13”), so you need to SUM sum this array and give the final result.

Be careful with this syntax, you can have no more than two criteria, two in the formula with the conditions "OR" ... and if there are two, then in one you must separate the criteria with commas , in the other half-columns .

If you need more, you can use SUMPRODUCT with MATCH , for example. in your case

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

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

Answer 2

You can use DSUM, which will be more flexible. For example, if you want to change the name of Salesman or Month Month, you do not need to change the formula, but only some criteria cells. For more details see the link below ... Even criteria can be a formula for copying from other sheets.

http://office.microsoft.com/en-us/excel-help/dsum-function-HP010342460.aspx?CTT=1

Answer 3

Quote_Month (Worksheet! $ D: $ D) contains the formula (= TEXT (Worksheet! $ E: $ E, "mmm-yy")) to convert the date / time number from another column to a text month link.

You can use OR by adding + to Sumproduct . See This

=SUMPRODUCT((Quote_Value)*(Salesman="JBloggs")*(Days_To_Close<=90)*((Quote_Month="Cond1")+(Quote_Month="Cond2")+(Quote_Month="Cond3")))

Screen shot

Answer 4

You might consider referencing the actual date / time in the source column for Quote_Month , then you could convert your OR to AND s, something like (casting the date to what I chose to call Quote_Date )

 =SUMIFS(Quote_Value,"<=90",Quote_Date,">="&DATE(2013,11,1),Quote_Date,"<="&DATE(2013,12,31),Salesman,"=JBloggs",Days_To_Close) 

(I moved interesting conditions to the front).

This approach works here because this "OR" condition actually indicates a date range - it may not work in other cases.

Answer 5

Speed

SUMPRODUCT faster than SUM arrays, i.e. having {} arrays in the SUM function. SUMIFS is 30% faster than SUMPRODUCT .

{SUM(SUMIFS({}))} vs SUMPRODUCT(SUMIFS({})) both work fine, but SUMPRODUCT little easier to write without CTRL-SHIFT-ENTER to create {} .

Preference

I personally prefer to write SUMPRODUCT(--(ISNUMBER(MATCH(...)))) over SUMPRODUCT(SUMIFS({})) for several criteria.

However, if you have a drop-down menu in which you want to select certain characteristics or all, SUMPRODUCT(SUMIFS()) , this is the only way. (as for the "all" option, the value should be entered in "<>" + "Any word you want, if it is not part of certain characteristics."

Answer 6

To make the formula work, place the cursor inside the formula and press ctr + shift + enter, and then it will work!

Answer 7

Using the following, you can easily associate a cellular address ...

 =SUM(SUMIFS(FAGLL03!$I$4:$I$1048576,FAGLL03!$A$4:$A$1048576,">="&INDIRECT("A"&ROW()),FAGLL03!$A$4:$A$1048576,"<="&INDIRECT("B"&ROW()),FAGLL03!$Q$4:$Q$1048576,E$2)) 

It can use the address / substitute / Column functions necessary to use cell addresses in full DYNAMIC.