Microsoft Excel: Use Conditional Wizard To Help With SUMIF

Problem: Conditional Formulas such as SUMIF and COUNTIF might be hard to figure out at first. CSE formulas needed to sum on the basis of two conditions are definitely hard to figure out. Is there an easier way?

Strategy: Excel offers a wizard that can walk you through building the formula. To install the wizard, Select Tools – Addins – Conditional Sum Wizard, as shown in Fig. 575.

Caution: This is no longer in the default install. You may need your installation CDs.

As shown in Fig. 576, the Add-In adds a new item to the bottom of the Tools menu called “Conditional Sum”.

1) Identify the range of your dataset in the Conditional Sum Wizard

– Step 1 of 4, as shown in Fig. 577.

2) Select the column that you wish to Sum in Step 2, as shown in Fig. 578.

3) Still in Step 2, build a condition using the dropdowns and choose Add Condition, as shown in Fig. 579. You can even add multiple conditions, as shown in Fig. 580.

4) As shown in Step 3 in Fig. 581, you can either create a single cell II with the answer or you can set up a range of cells with rep name, product name, and the formula for the answer. Choose the second option. This will allow you to change Ben to Amy and have the formula update.

5) Note in Fig. 582 that you now have a six-step wizard instead of the four-step wizard you had in Fig. 581. This is due to the preceding answer, where you selected “Copy the formula and conditional values”. In Step 4 of 6, you choose where to put the first field.

6) Step 5 asks where the product should go. Your answer should be Sheet1!$D$101.

7) Step 6 asks where the final formula should go, as shown in Fig. 583.

Result: The wizard has allowed you to build an incredibly complex CSE formula, as shown in Fig. 584.

After the wizard has built the first formula, you can enter a table of reps and products and copy the formula down to the other rows, as shown in Fig. 585.

Gotcha: When you copy cell E101, be sure that your paste range starts in E102. Normally, you could paste in E101:E112, but there is a limitation on CSE or Array formulas. If you attempt to paste E101 on top of itself, you will get the following error, as shown in Fig. 586: “You cannot change part of an array.” To get around this, copy E101 and paste to E102:E112.

Additional Information: If you edit the result of the Conditional Sum Wizard, you cannot complete the edit by using Enter. You have to hold down Ctrl+Shift while hitting Enter.

Summary: The Conditional Sum Wizard is a fantastic tool for building complex formulas based on one or more conditions.

Commands Discussed: Tools – AddIns; Tools – Conditional Sum