Using Conditional Sum Wizard pg 227 228 229 230

30. Building An Array Formula using Conditional Sum Wizard

An Array Formula is a formula technique that can be used to sum values in a column based on items in more than one column. It is an alternative to using the SUMPRODUCT formula.

We will use a sample to show how to use an Array Formula to get the total of the values in one column (QTY) where two conditions are present in the same row ie column MTH contain item '1' and column ITEM PURCHASED contain item 'Gum X'.

Using Conditional Sum Wizard to Build an Array Formula
(Removed In Excel 2010),

1. Activate the cell in which that you want the have the formula.
2. Click menu Tools/Add-Ins.
3. The Add-Ins dialog box will appear.

Add-Ins						?X

						OK
Add-Ins available:				Cancel
	Access Links				Browse...
	Analysis ToolPak
	Analysis ToolPak - VBA
	Autosave Add-in
	Conditional Sum Wizard
	Euro Currency Tools
	Internet Assistant VBA
	Lookup Wizard
	Macro Systems Add-ins Manager
	MS Query Add-in
    
Conditional Sum Wizard
	Helps sum data in lists

4. If there is no check mark in the box to the left of Conditional Sum Wizard, click it once to insert the mark.
5. Click OK.
6. The dialog box will close.
7. Click menu Tools/Wizard/Conditional Sum. (If there is no check mark on the left of Conditional Sum Wizard in the earlier dialog, this menu will not be available).
8. After a while, the Conditional Sum Wizard Step 1 of 4 dialog box will appear.

Conditional Sum Wizard - Step 1 of 4	x

The Conditional Sum Wizard helps you write formulas that sum specific values in a column based on other values in the list.

	Region 	Code		Sales Amount
	North	Retail		$413			$413
	East	Wholesale	$166
	North	Retail		$538			$538
	North	Wholesale	$230
									+
										$951

Where is the list that contains the values to sum, including the column labels?
	DATA!A4:AC30000

	?	Cancel		Finish

9. In the box under the label Where is the list that contains the values to sum, including the column labels?, write the address of your data area.

10. Click the Next button.
11. After a while, the Conditional Sum Wizard Step 2 of 4 dialog box will appear.

228

Conditional Sum Wizard - Step 2 of 4

Which column contains the values to sum? Select the column label.

Column to sum:	QTY

Next, select a column you want to evaluate, and then type or select a value to compare with data in that column.

	Column:			is			This value:
	MTH				=			1

	Add Condition			Remove Condition
	MTH=1
	ITEM PURCHASED=Gum X

	?	Cancel	< Back	Next >	Finish

12. In the box on the right of the label Column to sum, select the column which contains values we want to sum. In this case we select column QTY.

13. We don't want to sum all the values in the column QTY but we want to sum values which satisfy certain conditions that we set. We will sum values for which column MTH contain the item '1' and column ITEM PURCHASED contain the item 'Gum X' and both items must be available in the same row. We will enter the conditions in the box below the button Add Condition. We will enter the conditions one by one (initially the box is empty):

We want to enter the first condition, i.e. MTH=1.
Under the label Column, select MTH.
Under the label Is:, select =.
Under the label This value:, select 1.
Click Add Conditions.
The condition will be entered in the box.

229

Now we want to enter the second condition, ie ITEM PURCHASED='Gum X'.

Under the label Column, select ITEM PURCHASED.
Under the label Is:, select =.
Under the label This value:, select Gum X.
Click Add Conditions.
The condition will be entered in the box.

Now there are 2 conditions in the box.

(To remove a condition, click at the condition, then click button Remove Condition).