Excel Basics - How to use the Conditional Sum wizard

Posted on April 10, 2007 at 4:34 pm

Make sure to read other posts from the Excel Tutorials series!

If you’re new to Excel and want to learn how to use it, check out my other posts first on the ABSOLUTE basics by clicking on “MS Office Tips” on the right hand menu. In this little tutorial, we’re going to go through how you can use a handy little feature to perform a condition sum in Excel.

So what is a conditional sum? Let’s say you have some employees, John, David and Craig and you have some sales figures for them over a period of weeks or months in an Excel sheet and you want to see the total sales for each salesperson separately without having to use filters or do other complicated stuff, then you can use a conditional sum and specify the condition to be used and the data to sum.

It’s not that easy to understand when you read it, so let’s take a look at an example. Let’s say we have the following data in our Excel sheet:

Now in order to get the sum of the sales for just John or just David, you’d have to type in the SUM function and then manually choose each row for that particular person! Now that’s prone to error and wastes a lot of time. So let’s use conditional sums. First we need to activate this feature, since it is not enabled by default in Excel.

Go to Tools and click on Add-ins.

In the list that comes up, you should see a checkbox for “Conditional Sum Wizard“. Make sure to check that option.

Click OK and you’ll now find that the option for Conditional Sum is under the Tools menu. You don’t have to restart Excel or anything like that.

Now to get started, choose Conditional Sum for the Tools menu. The wizard has 4 steps and they are kinda straightforward, but it took me even two or three tries before I got the thing to work correctly! So I’ll explain as we go along…Firstly, on step 1 of 4, choose the entire data set including the column headers. You want to choose all the columns because later on you have to tell which column is going to be used for the condition and which column is to be summed up. As you can see, I selected all three columns from row 1 to row 13.

In Step 2 of 4, you are asked which column you want to sum and which column you want to evaluate. Excel was smart enough to choose the “Sales Amount” column as my sum column. However, I had to change the evaluate column to Salesperson and then choose the exact value for which I wanted the sum to be calculated for. In this case, I chose David, so that we can see the sum of David’s sales. Click the Add Condition button to add the condition to the condition list. As you can see, you can add more than one condition, so if you have other columns and you want to filter it down even more, you can do that by just choosing another column and picking a value.

Next, on Step 3, you can choose whether to just put the formula in the sheet or to also put conditional value (David) into the sheet. I just chose the formula.

Finally, just choose where you want the data to be placed on your sheet and you’re done!

And now you have the sales for only David without having to use filters or copy and paste the data somewhere else.

Let me know what other things you would like to learn about by posting a comment!

If you enjoyed this post, make sure you subscribe to my RSS feed!

» Filed Under MS Office Tips

4 Responses to “Excel Basics - How to use the Conditional Sum wizard”

nyla said on : September 21st, 2007 at 7:49 pm

Hey,

I like your site. I learn a lot of cool things on excel. Can you show how to use advance functions like what if analysis, pivot tables. I need to know more advance functions of excel. It’s hard to find tutorials on those topic categories.

Thanks

Greg said on : March 5th, 2008 at 8:33 am

Hi,

May I ask you how would it be possible to have lists of possibilities where the total of sales amount will have an exact amount, please?

I mean that I want for example 1500USD as the total amount of the Sales Amounts and list how is it possible to do. For example: 1. possibility: 1st 4th and 5th row will give a total amount of 1500. 2. possibility: 2nd, 4th and 6th will also give the total amount 1500. I need the list of these possibilites.

Could you help please?

Thanks a lot!

Best regards,

Greg

Tom Johnson said on : March 24th, 2008 at 3:20 am

Hi there,
Have just come across your site and find the tips very good. Is it possible with Excel conditional formatting to set up a spreadsheet to keep me updated about which invoices are paid on time or overdue.
For example: I do my invoices in Excel and give each invoice it`s own ref. No. I would then like to create a spreadsheet or worksheet that would list all of my invoices and to be able to highlight which ones have been paid, which are overdue etc. without having to search for and look at each individual invoice.

hope you can help,

Tom Johnson