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!

Microsoft Excel Basics Tutorial – Learning how to use Excel
Excel Tips & Tricks – How to unprotect only a portion of an Excel spreadsheet
Excel Basics – How to use the Conditional Sum wizard
How to use Data Validation in Microsoft Excel to create dropdown lists
Excel Tutorial – How to make a simple graph or chart in Excel
Excel Tutorial – Example of how to use the VLOOKUP function

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!

[tags]Microsoft excel, how to use excel, conditional sum wizard excel, excel basics[/tags]

» Filed Under MS Office Tips

Comments

9 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

Ali said on : June 17th, 2008 at 5:17 am

Thank you for the tutorial. I was not aware of this wizard. Using this wizard will redcuce the size of my spreadsheet dramarically.

Kong Thao said on : June 18th, 2008 at 1:12 pm

I am trying to use the conditional format wizard to sort by metal gauge but I get “-” for values less than 1, i.e. 0.063, 0.051, 0.080 etc. I have set these cells to be viewed as text but still get a zero when there should be a value.

ex

Material | Gauge | Pounds of Material

ALUM | 0.063 | 25

(should return 25, but returns “-”)

John Douglas said on : October 2nd, 2008 at 2:13 pm

Thanks for the tutorial on the conditional sum wizard. I am having a problem with it though. I have a report that is furnished to me that looks much like your example. Everytime I use the wizard it takes me through the steps and it appears fine. However, it always returns a value of 0 even if there is a different value in the column I ask it to sum. When I create my own simple speadsheet to check myself and go through the wizard it works fine. Any idea what could be wrong with the data on the provided report that is causing the 0 value to always come up? Thanks.

Nathiya said on : January 27th, 2009 at 10:05 am

Hi,
In our office we update the daily productivity in excel sheets. Each sheet contains that particular day productivity. At month end only we get the total productivity result. Here i want to know the formula or macros to sum the daily of productivity of the particular employee in the last sheet. So daily we know that month productivity instead of knowing it at the end of the month..