Use Conditional Sum Wizard To Enter CSE Formulas
December 09, 2004
One of the common questions at the MrExcel Message Board is how to use the SumIf function with two different conditions. Unfortunately, the answer is that SumIf can not handle two different conditions.
In order to do two conditions, you have to use a rather complicated array formula. The conditional sum wizard add-in allows you to enter these complicated formulas with ease.
Here is an Excel worksheet with columns for product, sales rep and sales. The data is in cells A2:C29.
If you want to total the sales, a simple SUM() function will work. =SUM(C2:C29).
Many Excellers discover the SumIf function. Using this function, it is fairly easy to figure out the total of sales for product ABC. =SUMIF(A2:A29,E2,C2:C29)
It is also easy to figure out the total of sales made by sales rep Joe with =SUMIF(B2:B29,E2,C2:C29).
You would then assume that it is possible to figure out the total sales of product ABC made by Joe. However, there is no way to do this with the SumIf function. It turns out that you need to use a fairly complex array or CSE formula.
Let's face it - the Sum formula is Excel 101. The SumIf formula is not far behind in complexity. However, the CSE formula to calculate the total of ABC sales made by Joe is enough to make even my head spin.
The good news - Microsoft offers the Conditional Sum Wizard which allows even a novice to enter complex conditional formulas based on 1, 2 or more conditions. The Conditional Sum Wizard is an add-in. To add this functionality to Excel, go to the Tools menu and select Add-Ins. In the Add-Ins dialog box, choose the checkbox next to Conditional Sum Wizard and choose OK. It is possible that you might need your installation CD at this point, because Microsoft does not include the wizard in the default install.
Once the add-in is successfully turned on, there will be a Conditonal Sum... choice near the bottom of the Tools menu.
Select a single cell in your dataset and choose Tools - Conditional Sum. Assuming that your data is nicely formatted with a single row of headings, Excel will properly guess at the range of your data. Choose Next.
In step 2, select the column to sum. In this case, the wizard already guessed you want to sum the first (and only) numeric column - Sales. In the middle of the dialog box are three dropdown controls. These happen to be correct for the first condition - Product equals ABC, so choose the Add Condition button.
Then you can add your second condition. In this case, you want to specify that the Sales Rep is Joe. Choose the arrow for the first dropdown. Excel offers an alphabetical list of the column names available. Choose Sales Rep.
The center dropdown is correct, but for completeness here, you can see that you could have chosen equal, less than, greater than, less than or equal, greater than or equal, or not equal.
From the third dropdown, select Joe.
Choose the Add Condition button.
You are now ready to go to Step 3. Press the Next button.
In step 3, you have two choices. In the first choice, the Wizard will enter a single formula with the values "ABC" and "Joe" hardcoded into the formula. It will give you the answer, but there will be no opportunity to easily change the formula. With the second choice, Excel will set up a new cell with the value "ABC" and a new cell with the value "Joe". A third cell will contain the formula that does a conditional sum based on those two values. With this option, you could type new values into the cells in order to see the total XYZ's sold by Adam.
The wizard will then ask where you want the value for ABC. Select a cell and choose Next. Repeat as the Wizard asks you to select a cell for Joe and the formula.
When you choose Finish in the last step, Excel will create a slightly different (but valid) version of the CSE formula.
This formula calculates that Joe sold $33,338 of ABC.
If you change the product input cell from ABC to DEF, the formula will re-calculate to show that Joe sold $24,478 of DEF.
The Conditional Sum Wizard puts complex formulas well within reach of all Excel owners.
Additional Information: If you want to build a table that will show sales of each product by each sales rep, there is some special "care and feeding" that you will need to know about these formulas. Type each sales rep across the top of the range. Type each product down the left column of the range. Edit the formula provided by the wizard. In the image below, the formula is pointing a the product in cell E6. This reference really needs to be $E6. If you leave the reference as E6 and copy the formula to column G, the formula would look at F6 instead of E6 and this would be wrong. Adding a dollar sign before the E in E6 will make sure that the formula always looks at the product in column E. The formula is also pointing to a sales rep in cell F5. This reference really needs to be F$5. If you left the reference as F5 and copy down to row 7, the F5 reference will change to F6 and this is not right. Adding a dollar sign before the row number will lock the row number and the reference will always point to row 5.
In Edit mode (select the cell and press F2 to edit), type a $ before the E. Type a dollar sign before the 5 in F5. Do not hit Enter yet!
This formula is a special type of formula. If you press Enter, you will get a 0, which is not correct.
Instead of typing Enter, hold down the Ctrl and Shift keys while you press Enter. This magic combination of Ctrl + Shift + Enter is why I call these CSE formulas.
There is one last consideration before copying the formula to the rest of the table. Your inclination might be to copy F6 and paste to F6:G8. If you try this, Excel will give you the puzzling "You Can Not Change Part of an Array" message. Excel is complaining that you can not paste a CSE formula into a range that contains the original CSE formula.
It is easy to work around this. Copy F6. Paste to F7:F8.
Copy F6:F8. Paste to G6:G8. You will have a table of CSE formulas showing totals based on two conditions.
