Using ‘Conditional Sum Wizard’ to build an Array Formula

Using ‘Conditional Sum Wizard’ to build an Array Formula

An Array Formula is a formula technique that can be used to sum values in a column based on items in more than one other column.

We will use a sample to show how to use Array Formula to get the total of 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’.

1. Activate the cell that you want the have the formula.
2. Click menu ‘Tools/ Add-Ins’.
3. A dialog with title ‘Add-Ins’ will appear.


4. If there is no mark ‘right’ in the box to the left of ‘Conditional Sum Wizard’, put the mark by clicking it once.
5. Click OK.
6. The dialog will disappear.
7. Click menu ‘Tools/ Wizard/ Conditional Sum’. (If there is no ‘right’ mark on the left of ‘Conditional Sum Wizard’ in the earlier dialog, this menu will not be available).
8. After a while, a dialog with title ‘Conditional Sum Wizard – Step 1 of 4’ will appear.

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 button ‘Next’.
11. After a while, a dialog with title ‘Conditional Sum Wizard – Step 2 of 4’ will appear.

12. In the box at the right of the label ‘Column to sum’, select the column which contain values we want to sum. In this case we select column ‘QTY’.
13. We don’t want to sum all values in the column ‘QTY’ but we want to sum values which satisfy certain conditions that we set. Our conditions is that we will sum values which columns MTH contain item ‘1’ and column ITEM PURCHASED contain 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, ie 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.

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.

We don’t want to enter more conditions.
(To remove a condition, click at the condition, then click button ‘Remove Condition).

14. Click ‘Next’.
15. After a while, a dialog with title ‘Conditional Sum Wizard – Step 3 of 4’ will appear.

16. Select the option label ‘Copy just the formula to a single cell’.
17. Click ‘Next’.
18. After a while, a dialog with title ‘Conditional Sum Wizard – Step 4 of 4’ will appear.

19. Under the label ‘Type or select a cell and then click Finish’, enter the cell address that you want the result to appear.
20. Click ‘Finish’.
21. You will see the result in the cell that you identify in step 19. 22. You can see the formula in the formula bar. You will notice that the formula is preceded with “{“ sign and ended with “}” sign, which indicate that the formula is an Array Formula. If you understand the mechanic of Array Formula, you don’t have to use the ‘Conditional Sum Wizard’ to build the formula. If you edit the cell containing an Array Formula, the braces sign “{“ and “}” will disappear (to put back the formula into an array formula, press ‘Ctrl’, ‘Shift’ and ‘Enter’ keys simultaneously).