Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

Excel Conditional Sum Wizard

| | Information Helpful? Why Not Donate.

Summing by More Than 1 Criteria

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only ~~$145.00~~. $59.95 Instant Buy/Download, 30 Day Money Back Guarantee & Free Excel Help for LIFE!

Got any Excel Questions? Free Excel Help . See Also: Sum With Multiple Criteria and Count With Multiple Criteria

Excel Sum Wizard

The Conditional Sum Wizard is an Add-In to Excel that is used to summarise values in a list based on set criteria. An example of how the Conditional Sum Wizard can be used is on a list of data that contains basketball teams and number of points scored over a period of time. By using the Conditional Sum Wizard you can create a formula to add the total points for a team over a specified period.

Because the Conditional Sum Wizard is an Add-In to Excel, you will need to install it (you may need your Office disk to do this). To install the Conditional Sum Wizard go to Tools>AddIns, then select Conditional Sum Wizard until it has a tick in the box to the left, then click OK. Once done the Conditional Sum Wizard will be available to you under the Tools Menu.

USING ONE CONDITION

Let�s say we have a list of dates from A2:A32 (with a heading of Dates in A1, bolded and centered). From B2:B32 is a list of basketball teams (with a heading of Teams in B1 that has been bolded and centered) and the points recorded in C2:C32 (heading of Points in C1 also bolded and centered). We want to find out the total points scored for the Black Crows basketball team.

Now click inside your list range and to go Tools>Conditional Sum Wizard. A Wizard is a mini program that steps you through a process and you should be looking at Step 1 of the Wizard, which asks the question;

Where is the list that contains the values to sum, including the column labels?

Because you were already clicked inside your list when you activated the Conditional Sum Wizard, and because your headings are defined as different to your list, Excel, will automatically pick up your list range of $A$1:$C$32, so all you need to do here is click the Next button.

This brings you to Step 2 of the Wizard which asks you:

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

We need to select Points from the drop down list as this is the column in which we are looking for our values to sum.

Next we are asked to select a column that we wish to evaluate, and then type or select a value to compare with data in that column.

Make the following selections;

Under column � Select Team
Under Is: - Select =
Under This Value - Select Black Crows

Now select Add Condition. This will add the condition to the Conditional Sum dialog

Click the Next button to take you to Step 3 of the Wizard.

In Step 3 we are asked in which form we would like the formula copied to our worksheet. There are two choices;

Copy just the formula to a single cell
Copy the formula and conditional values

We will accept the default, copying the formula to a single cell.

Click the Next button to take you to the final step (Step 4) of the Wizard where we are asked to type or select a cell.

Select cell G2 and click Finish.

You will notice when you click finish and can view the formula in cell G2 of your worksheet that it has been pasted as an ARRAY formula .

USING TWO CONDITIONS

You can easily use more than one condition with the Conditional Sum Wizard. Let�s see how many points were scored overall between 25 March and 26 April 2006.

Click inside your list and go to Tools>Conditional Sum Wizard. Your list range will be automatically picked up. Click Next to go to Step 2.

Under Step 2, select Points as the column to sum at the top of the dialog.

Make the following changes for the columns you with to evaluate:

Under column � Select Date
Under Is: - Select >=
Under This Value � type 25 March 2006

Now select Add Condition. This will add the first condition to the Conditional Sum dialog

Now to add the second condition:

Under column � Select Date
Under Is: - Select <=
Under This Value � type 26 April 2006

Again select Add Condition. This will add the second condition to the Conditional Sum dialog. Click the Next button

This will take you to Step 3 of the Wizard where we are asked in which form we would like the formula copied to our worksheet. This time we will select the second option; copy the formula and conditional values. Select the Next button.

In Step 4 of the Wizard you are asked to nominate a cell in which to paste your formula. Nominate cell G4 and click Finish. This will paste the first date (25/03/06) to cell G2. Now select cell H2 and again click Finish. This will paste the second date (26/04/06) to sell H2. Now select I2 and click Finish. This will paste the number of points scored between 25/03/06 and 26/04/06 to I2

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft