8

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?

pnuts
  • 58,317
  • 11
  • 87
  • 139
Dominic
  • 587
  • 3
  • 9
  • 19

7 Answers7

19

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

barry houdini
  • 45,615
  • 8
  • 63
  • 81
  • Thanks, both those methods work for me. However, is there a way to specify the month values in the array {"Oct-13","Nov-13","Dec-13"} by using references to text stored in another cell, e.g {Worksheet!G2,Worksheet!H2,Worksheet!I2}? – Dominic Nov 27 '13 at 11:05
  • Never mind, I figured it out. I replaced ISNUMBER(MATCH(Quote_Month,{"Oct-13","Nov-13","Dec-13"} with ISNUMBER(MATCH(Quote_Month,G2:I2 where G2, H2 and I2 are the cells containing the text Oct-13, Nov-13, Dec-13 – Dominic Nov 27 '13 at 11:48
  • That's right, you can use a range in both SUMPRODUCT and SUMIFS versions. If you do that in the SUMIFS version the formula then needs CTRL+SHIFT+ENTER unless you use SUMPRODUCT in place of SUM – barry houdini Nov 27 '13 at 11:55
  • Just stumbled across this post. Astonishing technique! Does anyone know why/how this works? Like, I get that MATCH(Quote_Month,{"Oct-13","Nov-13","Dec-13"},0) is used to produce a vector. Why this even works is a mystery to me. When I messed around with this, the most obvious first step for me was to count the number of hits, ie SUM the vector. It doesn't work. The fx wizard clearly shows the vector, and I can see the hits. SUM won't sum. But SUMPRODUCT will -why? Back to SUMIFS, I can see that the {} notation WILL work, but not any range reference (eg G2:I2) - why? Excel is weird. – Ollie2893 Oct 01 '14 at 12:03
  • PS: I am on Excel 2010 and {,,} will not work for me AT ALL. I must use {;;} to get any of this to work. – Ollie2893 Oct 01 '14 at 12:05
  • Well I never... Playing around with this, I find that SUMPRODUCT(SUMIFS(Values,Keys,G2:I2)) does the trick. The key to all this vector processing seems to be something that SUMPRODUCT does. – Ollie2893 Oct 01 '14 at 12:13
  • @Ollie2893 - have you got everything working? If you use `SUM`, rather than `SUMPRODUCT` then you can get the same result but the formula requires "array entry", i.e. with CTRL+SHIFT+ENTER – barry houdini Oct 01 '14 at 14:22
  • I be damned. You are quite right, Barry. {=SUM(SUMIFS(...))} works too. What do you think is more efficient - =SUMPRODUCT() or {=SUM()}? – Ollie2893 Oct 03 '14 at 10:24
  • Actually, I gonna answer my own question. Efficiency doesn't much matter. Entering and editing array formulas in Excel is counter-intuitve and the consequences of an inadvertent, plain ENTER catastrophic. My number of criteria (G2:I2) varies from col to col. It is far easier to use SUMPRODUCT, press F2, adjust the range and press ENTER... – Ollie2893 Oct 03 '14 at 10:33
  • In this case I don't think there would be much to choose between them - SUMIFS does most of the work in both cases - if you have criteria as G2:I2 then SUM/SUMPRODUCT is only actually summing the 3 values produced by SUMIFS – barry houdini Oct 03 '14 at 10:38
  • @barryhoudini Is there a version of this for Google Sheets? – Tin Man Jan 15 '18 at 13:03
2

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

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

mooneazy
  • 68
  • 1
  • 8
1

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.

You can use OR by adding + in Sumproduct. See this

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

ScreenShot

enter image description here

Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
1

You might consider referencing the actual date/time in the source column for Quote_Month, then you could transform your OR into a couple of ANDs, something like (assuing the date's in something I've chosen 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 the interesting conditions to the front).

This approach works here because that "OR" condition is actually specifying a date range - it might not work in other cases.

Mike Woodhouse
  • 51,832
  • 12
  • 88
  • 127
1

Speed

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

{SUM(SUMIFS({}))} vs SUMPRODUCT(SUMIFS({})) both works fine, but SUMPRODUCT feels a bit easier to write without the CTRL-SHIFT-ENTER to create the {}.

Preference

I personally prefer writing SUMPRODUCT(--(ISNUMBER(MATCH(...)))) over SUMPRODUCT(SUMIFS({})) for multiple criteria.

However, if you have a drop-down menu where you want to select specific characteristics or all, SUMPRODUCT(SUMIFS()), is the only way to go. (as for selecting "all", the value should enter in "<>" + "Whatever word you want as long as it's not part of the specific characteristics".

Walter S
  • 11
  • 1
0

In order to get the formula to work place the cursor inside the formula and press ctr+shift+enter and then it will work!

user3685392
  • 1
0

With the following, it is easy to link the Cell 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))

Can use address / substitute / Column functions as required to use Cell addresses in full DYNAMIC.

Abdulla Nilam
  • 36,589
  • 17
  • 64
  • 85
Ganesh
  • 1