Microsoft Excel: Test For Two Conditions In A Sum

Problem: You need to sum a dataset based on two conditions. The SUMIF function can only handle one condition. As shown in Fig. 597, you want to write a formula that will total all sales by Amy of product ABC

See all Microsoft Excel tips

Strategy: You have to use a CSE formula to do this. This question comes up a lot at the MrExcel.com site. It is a definite collision point in Excel – it is a very common problem, but a very difficult solution.

The SUMIF Function will not do this for you. You have to use Boolean logic and a CSE formula. You want to test to see if each cell in B2:B99 is equal to B102. This would be represented by the following formula:

=($B$2:$B$99=$B102)

You also want to test to see if each cell in D2:D99 is equal to C101. This would be represented by the following formula:

=($D$2:$D$99=C$101)

If you multiply those two terms together, you will end up with a 1 wherever both conditions are True and a 0 wherever one condition is not True.

=($B$2:$B$99=$B102)*($D$2:$D$99=C$101)

As shown in Fig. 598, pretend that you actually entered these formulas in columns F, G, and H. After you have the 1 or 0 in column H, you have to multiply that result times Sales in column E and then sum up column I.

To multiply the Boolean terms by Sales, use:

=($B$2:$B$99=$B102)*($D$2:$D$99=C$101)*($E$2:$E$99)

To sum the result, use:

=SUM(($B$2:$B$99=$B102)*($D$2:$D$99=C$101)*($E$2:$E$99))

Enter this formula in C102. Instead of using Enter after typing the formula, use Ctrl+Shift+Enter. Excel will evaluate the formula as an array and produce the correct result, as shown in Fig. 599.

Note: You do NOT type the curly braces around the formula. Excel will add those when you use Ctrl+Shift+Enter.

Due to the careful use of the dollar signs in each reference, you’ve made a formula that can be copied to the rest of the table. Normally, you would copy C102 and paste it to C102:E105, but because of the limitation of CSE formulas, you cannot do this. You first have to copy C102 to C103: C105, as shown in Fig. 600.

Then, copy C102:C105 and paste it to D102:E105, as shown in Fig. 601.

Gotcha: CSE or array formulas are very powerful. They are also very memory intensive. Don’t go overboard with them. I once tried to build a report of 800 CSE formulas with each one totaling a 50,000-row dataset using three conditions. If I hadn’t rebooted the computer, it would still be trying to calculate the formula.

Alternate Strategy: Aladin Akyurek has written an excellent article about this topic at http://www.mrexcel.com/wwwboard/messages/8961. html. Aladin notes that this problem can be solved without using a CSE formula by using the SUMPRODUCT function. The equivalent formula for cell C102 would be as follows: =SUMPRODUCT(($B$2:$B$99=$B102)*($D$2:$D$99=C$101)*($E$2:$

E$99))

Summary: Using CSE formulas and Boolean logic, you can solve a problem where you need to sum on the basis of two conditions. This is not just a powerful extension of SUMIF. Using these types of formulas, you can write just about any conditional calculation that you can imagine.