Editor’s Note: To read more about spreadsheets, share a tip with other readers, or suggest a topic for Bill Jelen to cover in an upcoming column, click here to visit CFO.com’s Spreadsheet Tips Page.
Reader Greg T. wins a copy of Guerilla Data Analysis Using Microsoft Excel for his question: “I am often trying to compare similar information in different spreadsheets using vLookup; however, I consistently run across instances when I would like to have a multicondition vLookup. I have read that Excel has a formula writer. How do you use the formula writer to create a multiconditional vLookup (similar to a sumifs)? Thanks.”
Greg is remembering the old Conditional Sum Wizard. This wizard was created by Microsoft way back when conditional sum formulas required either SUMPRODUCT or an array formula. The Conditional Sum Wizard can be found up through Excel 2007, but it was not rewritten for 64-bit Excel 2010.
To activate the Conditional Sum Wizard, press Alt+T, then press I to display the list of add-ins. Check the box for Conditional Sum Wizard and click OK. The Conditional Sum icon will appear on the right side of the Formulas tab (see Figure 1, below).
Fig. 1
Follow these step to use the wizard:
1. Select one cell in your data and choose Conditional Sum.
2. Confirm that Excel has selected the entire data set. Click Next (see Figure 2).
Fig. 2
3. In Step 2 of the wizard, build each of the conditions. In Figure 3, the conditions are Product = C and Rep = Bob.
Fig. 3
4. Click Next to move to Step 3. Choose the option to copy the formula and the conditional values. This option will allow you to copy the formula for other conditions (see Figure 4).
Fig. 4
5. In the remaining steps, identify where the output cells should be. Click Finish.
As you will see in Figure 5, the resulting formula uses an array formula. If you edit this formula, you have to use Ctrl+Shift+Enter to complete the formula. But the resulting formula of =SUM(IF($B$2:$B$16=F3,IF($A$2:$A$16=E3,$C$2:$C$16,0),0)) is not the most efficient way of solving this problem.
Fig. 5
In Excel 2007 and later, you can use the new SUMIFS function as shown in Figure 6.
Fig. 6
If you have to share your workbook with people using Excel 2003 or earlier, you can use the SUMPRODUCT function as shown in Figure 7.
Fig. 7
Even though the Conditional Sum Wizard is no longer in Excel 2010, you can follow the examples above to build conditional sum formulas.
CFO contributing editor Bill Jelen is the founder of MrExcel.com and the author of 32 books about Excel. To hear more of Bill’s tips and tricks, tune into CFO.com for his May 5 Webcast on pivot tables. As always, you can win a copy of one of his books if your question is selected for a column. Post your question at the Community Center “Ask a Question” box on the right.