For more information about Setup, see Appendix A, Installing Microsoft Excel. in C#.NET

Creation QR in C#.NET For more information about Setup, see Appendix A, Installing Microsoft Excel.

The Conditional Sum Wizard creates formulas using the SUM and IF functions. This Wizard not only makes the construction of these formulas easier and faster, but also shows you how these formulas are constructed so that you can build your own conditional formulas without the Wizard.

To build a conditional formula:

12

1 Select the table or list containing the values you want to use, and choose Tools, Conditional Sum to display the dialog box shown in Figure 12-31. If you click anywhere in the table before you start the Wizard, Excel automatically selects the current region for you. If Excel selects the correct region, click Next. 394

Building Formulas Otherwise, drag to select the range you want to use. Remember to include the row and column labels. After clicking Next, the dialog box changes to the one shown on the left in Figure 12-32.

extract totals. This is why you need to select the labels in the Step 1 dialog box. If the column labels do not appear in the list, click Back and reselect the range.

Figure 12-31. The Conditional Sum Wizard helps you construct SUM formulas that are choosy about what they include.

12

Microsoft Office Excel 2003 Inside Out Note You can find the sample file used in this example, Pacific Sales Transactions.xls, on the companion CD.

want to include in the total. In the Column list, select the name of the column containing the labels you want to conditionally check, select an operator in the Is list, and then select a value in the This Value list. The contents of the This Value list change depending on the column selected in the Column list. The This Value list displays only the unique values in the selected column, ignoring duplicates.

The criteria you specify are added to the list at the bottom of the dialog box. You can add as many as seven conditions. If you change your mind about any condition, select the condition from the list and click Remove Condition. When you have finished removing conditions, click Next.

If you chose the Copy Formula And Conditional Values option in Step 3 of the Wizard, the Wizard adds an extra step. Select the cell where you want the conditional value to go. Then click Next, and click the cell where you want the formula to go.

Beware of the Wizard In many cases, including Step 1 of the Conditional Sum Wizard (shown in Figure 12-31), Excel attempts to select a cell range or table for you automatically. This doesn t always work the way it should. In the example worksheet shown in Figure 12-31, Excel automatically selected A1:C20, which includes a merged cell at the top; this isn t allowed. We had to select the correct range before proceeding. Excel should recognize this before automatically selecting it, but it doesn t.

You can add more conditional formulas, or if you already have a list of unique values (such as salesperson names), you can copy the formula as needed (but only if you used the Copy Formula And Conditional Values option in Step 3 of the Wizard), as shown in Figure 12-33.

12

Figure 12-33. We added a list of unique salesperson names (conditions) in column E and copied the conditional sum formula to cells F5:F9.

Watch out for spaces The Conditional Sum Wizard isn t smart about space characters. For example, if a label in the column of criteria includes an invisible space character at the end of the text string, Excel excludes it from the total, even if all the instances are the same.

The Lookup Wizard creates formulas using the INDEX and MATCH functions. Like the Conditional Sum Wizard, it makes the construction of lookup formulas easier and faster, and it also illustrates how these formulas are constructed so that you can build them yourself later. For more information about the INDEX and MATCH functions, see Understanding Lookup and Reference Functions on page 426. To build a lookup formula:

1 Choose Tools, Lookup to display the dialog box shown on the left in Figure 12-34.