Excel Tip: Conditional Sum Wizard

You are familiar with the Sum() function in Excel, and it allows you to sum all cells in a range. However, what if you would like to only include those cells where some condition is true. A "conditional" sum. Excel provides a nice graphical wizard to help us write conditional sum formulas, called, appropriately enough, the Conditional Sum Wizard. Full text has details.

The Conditional Sum Wizard is not installed by default, you must manually install it. To do so, simply go to Tools -> Add-ins, as shown in Figure 42 below.

Figure 42

Simply click the Conditional Sum Wizard checkbox, and click OK. Now, you can use it by selecting it from the Tools menu, as shown in Figure 43 below.

Figure 43

The Conditional Sum Wizard dialog box appears, as shown in Figure 44 below.

Figure 44

Step 1 requires us to identify the range of data.

Figure 45

Step 2, as shown in Figure 45 above, allows us to add multiple conditions. Step 3 allows us to specify where the results will be placed, as shown in Figure 46 below.

Figure 46

The final step 4 allows us to specifically identify the destination cell(s) to place the result.

Figure 47

Finishing out the wizard places the complex formula required to compute the sumif you specified during the wizard. This is convenient since it is a formula. Now, if the underlying data is changed or updated, the formula will be updated instantly. Also, you can view the formula and modify it to alter the criteria. The formula that the wizard above produced is shown in Figure 48 below.

Figure 48

As you can see by the complexity of the formula, we are fortunate that Microsoft has provided us with the graphical wizard to write it!