You are here: Home / Archives for Sum

How to use the conditional sum wizard

March 17, 2009 By

The Conditional sum wizard helps you create complex formulas to sum values in a column based on certain criteria. The wizard is part of Excel Add-ins. You must load it first before it can show up in the Tools menu. See then bottom of this post if you want to know how to load the wizard.

The following example will demonstrate how to use the conditional sum wizard:

Suppose we have data of total sales by month and product as shown below, and we want to sum the total sales for Product3 in the first quarter (months 1 to 3).

1. On the Tools menu click Conditional Sum. The dialogue for step1 will open.

csumwizard1

2. Click on the text box then select your data range, including column headings.
3. Click next. The dialogue for step2 will open.

csumwizard2

4. Specify your conditions by manipulating the three combo boxes for Column, Is and This value. Click Add condition every time.

5. When finished with your conditions click next. The dialogue for step 3 will open.

csumwizard3

6. Specify whether you want to copy your generated formula only, or both formula and the values of your conditions.

7. Click next. The dialogue for step 4 will open.

csumwizard4

8. Specify the cell(s) where you want your generated formula (and values of conditions if applicable) to be copied.

9. Click finish. The formula will be copied to the specified cell, and the result of the formula will be shown. Here is the formula in our example:

{=SUM(IF($A$2:$A$19<4,IF($B$2:$B$19=”Product3″,$C$2:$C$19,0),0))}

How to Load the Conditional Sum Wizard

The Conditional Sum Wizard is not loaded by default. To determine whether it has been loaded, on the Tools menu, look for the Wizard menu item. If it is not present, or if when you point to it, the item Conditional Sum is not present, you need to load the add-in.

To load the Conditional Sum Wizard, follow these steps:

1. On the Tools menu, click Add-ins.
2. In the Add-Ins available list, select Conditional Sum Wizard, and then click OK.

Applies to Excel 2003

Filed Under: Formulas, Functions Tagged With: , ,

How to sum values based on multiple criteria

November 18, 2008 By

The SUMIF function is a direct way to sum values based on a single criteria. If we want to sum or add  values based on multiple criteria  however, then we need to take extra steps. One alternative for this is to use the SUM function and the IF function.

In the example below we want to sum up the total sales for “John” in quarter 2 (Q2). i.e. our criteria is “John” in column B and “Q2″ in column C. We will use the following formula for this purpose:

=SUM(IF($B$2:$B$17=”John”,IF($C$2:$C$17=”Q2″,$D$2:$D$17,0),0))

However this formula should be an “Array formula”. To make it an “Array formula” you should press CTRL+SHIFT+ENTER while you are in the edit mode of the formula. It should then look like this :

{=SUM(IF($B$2:$B$17=”John”,IF($C$2:$C$17=”Q2″,$D$2:$D$17,0),0))}

This formula will give us a result of 9547 (summing sales in the yellow rows).

Note:

The formula presented in this post is generated using the conditional sum wizard. You can try the wizard for yourself.

If you have the Analysis Toolpak installed, then the wizard should be available in the Tools menu. If not then read more on: How-to-install-and-load-the-analysis-toolpak.

Filed Under: Data, Formulas Tagged With: , ,

How to sum values based on a criteria or condition

November 13, 2008 By

To sum values based on a specified criteria or condition you use the SUMIF function, and its syntax is as follows:

SUMIF(range,criteria,sum_range)

Range is the range of cells that you want to be evaluated using the specified criteria.

Criteria is the criteria in the form of a number, expression, or text that defines which cells will be added or summed.

Sum_range are the actual cells to add if their corresponding cells in the range match criteria.

In the following example if we want to sum all the sales for Product3 then we will use the following formula:

=SUMIF(A2:A13;”Product3″;C2:C13)

I.e. we will search for “Product3″ in the range A2:A13. When it is found then the corresponding value (in the same row) from the range C2:C13 will be added to the sum. 

This will give a result of 465 (77 + 200 + 188).

Filed Under: Functions Tagged With: , , ,

Count how often a value occurs – multiple criteria

October 20, 2008 By

In a previous post I covered counting how often a value occurs within a range. In that post we looked into one criteria, and we used the COUNTIF function.

But what if we want to count cells that meet multiple criteria. We can not use the COUNTIF function  in this case, so we will take a different approach and use the SUM function.

In the example below we want to count how often “John” appeared in the list in quarter 2 (Q2). i.e. our critera is “John” in column B and “Q2″ in column C. We will use the following formula for this purpose:

=SUM((B1:B17=”John”)*(C1:C17=”Q2″))

However this formula should be an “Array formula”. To make it an “Array formula” you should press CTRL+SHIFT+ENTER while you are in the edit mode of the formula. It should then look like this :

{=SUM((B1:B17=”John”)*(C1:C17=”Q2″))}

This formula will give us a result of 3 as you can see.

Filed Under: Data, Formulas, Functions Tagged With: , ,