
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 AddIn 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 AddIn 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 Addins 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
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
Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages  Trading In Excel  Construction Estimators  Finance Templates & Addins Bundle  CodeVBA  SmartVBA  PrintVBA  Excel Data Manipulation & Analysis  Convert MS Office Applications To......  Analyzer Excel  Downloader Excel
 MSSQL Migration
Toolkit 
Monte Carlo Addin 
Excel
Costing Templates