Is a SUMIF Multiple Formula a Problem for you? It won’t Be since Now…

You may be struggling with SUMIF Multiple but there is no need to worry about it, Excel has more than 7 ways to do it.

In this article you will learn 7 styles to do an effective SUMIF Multiple.

This is an excellent article. I have dealt with the exact issues mentioned here and had not considered many of the options mentioned.I am truly interested to discover what other suggestions and ideas the author may be able to share.Robert Parker, Project Manager at LeTourneau Technologies Longview, Texas Area. Posted on June 19, 2009 in the LinkedIn group Microsoft Excel Users.

As in Math, there is not a unique way to solve a problem in Excel; give a look at these 7 types of SUMIF Multiple techniques and use the option you prefer.

Let’s start…

Learn how to create a SUMIF AND 2 and SUMIF AND 3 criteria.The first 5 alternatives sums [Amount] per [Contract Date] and per [Product Id]. See B15:C16 in graphic 1. The other two ones do it but quite different.

Graphic 1

1. SUMIF with Helper Columns – A Magical Solution

Employ this choice if you love Excel SUMIF function or if you are not proficient yet with Array Formulas or SUMPRODUCT.

Well…

How do you evaluate two/three conditions if SUMIF allows just one criterion?

This is the secret: the weakness lies in the “criteria” argument (just one condition) so strengthen the “range” argument. See the syntax: SUMIF(range, criteria, [sum_range]). How do you make it stronger? Create a new “range” column.

Here are the formulas, see graphic 1.

  • B15 formula is =SUMIF($F$2:$F$8;”C-01TRUE”;$B$2:$B$8)
  • B16 formula is =SUMIF($G$2:$G$8;”C-01TRUE”;$B$2:$B$8)

2. SUMPRODUCT – A Versatile SUMIF Multiple Solution

SUMPRODUCT is a built-in array formula; it behaves like it but without pressing CTRL + SHIFT + ENTER.

Here are the SUMIF Multiple formulas, see graphic 1.

  • B15 formula is =SUMPRODUCT(($A$2:$A$8<DATEVALUE(“05/01/2009”))*
    ($D$2:$D$8=B$14)*$B$2:$B$8)
  • B16 formula is =SUMPRODUCT(($A$2:$A$8>=DATEVALUE(“05/01/2009”))*
    ($A$2:$A$8<=DATEVALUE(“05/31/2009”))*($D$2:$D$8=B$14)*$B$2:$B$8)

How do you evaluate two/three conditions? It is very simple: the above solution is something like this: SUMPRODUCT(1*1*1*[Amount]), what does this mean? If one of the conditions is false (zero) you would have for example: SUMPRODUCT(1*0*1*[Amount]) and the corresponding row will not be summed.

The above function is a SUMIF AND with three conditions.

That’s not all…

Build a SUMIF OR by using + instead of *, of course the logic must be ok. Why? A correct OR situation return a sole TRUE so you have something like this: SUMPRODUCT((1+0+0)*[Amount]). Two TRUE conditions duplicate your result: SUMPRODUCT((1+0+1)*[Amount]).

You can use an Array Formula instead of Excel SUMPRODUCT function, see below…

3. Array Formula with SUM Function- A Mystical Solution

Here are the formulas, see graphic 1.

  • B15 formula is {=SUM(($A$2:$A$8<DATEVALUE(“05/01/2009”))*
    ($D$2:$D$8=B$14)*$B$2:$B$8)}
  • B16 formula is {=SUM(($A$2:$A$8>=DATEVALUE(“05/01/2009”))*
    ($A$2:$A$8<=DATEVALUE(“05/31/2009”))*($D$2:$D$8=B$14)*$B$2:$B$8)}

What is passed to the SUM function above? Each corresponding row from $B$2:$B$8 is passed to be summed (If the three conditions are met). You will have something like this: SUM(1*1*1*[Amount]), a SUMIF Multiple 3 criteria function.

To build a SUMIF OR substitute the * by + and add additional parenthesis ((1+1+1)*[Amount]). Of course the logic must be accordingly.

4. Array Formula with Excel SUM and IF – Another Mystical Solution

Here are the formulas, see graphic 1.

  • B15 formula is {=SUM(IF($A$2:$A$8<DATEVALUE(“05/01/2009”);IF($D$2:$D$8=B$14;$B$2:$B$8)))}
  • B16 formula is {=SUM(IF($A$2:$A$8>=DATEVALUE(“05/01/2009”);
    IF($A$2:$A$8<=DATEVALUE(“05/31/2009”);IF($D$2:$D$8=B$14;$B$2:$B$8))))}

What is passed to the SUM formula? Each corresponding row from $B$2:$B$8 is passed to be summed (If the three “value_if_true” are TRUE).

Let’s review the formula in detail…

First, see the IF function syntax: IF(logical_test, value_if_true); the formula above omitted the optional “value_if_false” argument.

In short, the formula could be expressed this way: =SUM(IF1(Test 1,IF2(Test 2,range to be summed). You can nest as many IF in each “value_if_true” argument; the very last “value_if_true” must be the range to be summed $B$2:$B$8.

Important notice about Array Formulas:

  1. Remember to press CTRL + SHIFT + ENTER every time you enter or edit an array formula
  2. Array Formulas are difficult to comprehend; consequently, be sure users understand how to change them
  3. Large Array Formulas can retard computations. This also applies to SUMPRODUCT

5. DSUM as Another SUMIF Multiple

These formulas are short and easy to write but they require criteria ranges that may turn your workbook unwieldy.

Here are the SUMIF Multiple formulas, see graphic 2

  • B15 formula is =DSUM($A$11:$D$18;”Amount”;$A$1:$D$2)
  • B16 formula is =DSUM($A$11:$D$18;”Amount”;$A$5:$D$6)

Above formulas sum the values from [Amount] field that matches the given criteria (A1:D8).

Graphic 2

6. Excel Pivot Tables – A Powerful and Resourceful Solution

This way to sum values based on multiple criteria is potent but not appropriate for sending in reports. Use it for your personal analysis and data crunching tasks.

Send Pivot Table reports when your target users know how to use this tool.

Here’s how to proceed…

Arrange the Pivot Table Field List this way:

  • Row labels: [Contract date]
  • Values: sum of [Amount]
  • Column labels: [Product Id]

The final solution requires a Date filter, apply it in A14. You can create helper columns and drop the fields appropriately to produce a better grouping.

Graphic 3

7. Excel Filter – The Most Flexible Way to Sum Values Based on Multiple Criteria

You can achieve everything with Excel Filter, just set it in the correct sequence to get quick/complex answers. Split the table if necessary to go deeper in data.

Maybe future Excel versions will have a Save Filter Command in which case you will be enable to send reports and target users will interpret them easier.

Helper columns enhance the power of your Excel Filter; nothing is carved on stone so use this technique when you need it.

Graphic 4

Conclusion

As you see, Excel is bottomless…

As a self-taught user you must be always learning new things to do the old and recurrent problems more efficiently and comfortably.

It is also true that knowing some formulas will take you to the next level. This is the case with Lookup Formulas


Posted on June 19, 2009 in the LinkedIn group Microsoft Excel Users.This is an excellent article. I have dealt with the exact issues mentioned here and had not considered many of the options mentioned. I am truly interested to discover what other suggestions and ideas the author may be able to share.”

 

Robert Parker
Project Manager at LeTourneau Technologies Longview, Texas Area