Excel Conditional Sum Wizard. VBA Special Cells Method

The Conditional Sum Wizard is an Add-In to Excel that is used to summarize 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

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!

The SpecialCells Method in Excel VBA

One of the most beneficial Methods in Excel (in my experience) is the SpecialCells Method. When used, it returns a Range Object that represents only those type of cells we specify. For example, one can use the SpecialCells Method to return a Range Object that only contains formulae. In fact, we can, if we wish, even narrow it down further to have our Range Object (containing only formulae) to return only formulae with errors.

The syntax for the SpecialCells Method is;
expression.SpecialCells(Type, Value)

Where "expression" must be a Range Object. For example Range("A1:C100"), ActiveSheet.UsedRange etc.

Type=XlCellType and can be one of these XlCellType constants.
xlCellTypeAllFormatConditions. Cells of any format
xlCellTypeAllValidation. Cells having validation criteria
xlCellTypeBlanks. Empty cells
xlCellTypeComments. Cells containing notes
xlCellTypeConstants. Cells containing constants
xlCellTypeFormulas. Cells containing formulas
xlCellTypeLastCell. The last cell in the used range. Note this XlCellType will include empty cells that have had any of cells default format changed.
xlCellTypeSameFormatConditions. Cells having the same format
xlCellTypeSameValidation. Cells having the same validation criteria
xlCellTypeVisible. All visible cells

These arguments cannot be added together to return more than one XlCellType.

Value=XlSpecialCellsValue and can be one of these XlSpecialCellsValue constants.
xlErrors
xlLogical
xlNumbers
xlTextValues

These arguments can be added together to return more than one XlSpecialCellsValue.

The SpecialCells Method can be used in a wide variety of situations when you only need to work with cells housing specific types of data. For example, the code below would return a Range Object representing all formulae on the active Worksheet.

If we wanted, we could narrow this down further to only return a Range Object representative of all formulae that are returning numbers.

Once we have the specific Range Object type returned we can then work with only those cells. This can often be done in one line of code, or you may need to loop through the range. See examples below;

Although I have used a loop on the second macro, so that all returned numbers are converted to their negativecounterparts, we could make use of PasteSpecial to do so without looping and allow the formulae to remain in the cells. That is;

SpecialCells Gotcha!

If you are familiar with Excel and it's built in features, such as SpecialCells, you will know that when/if one specifies only a single cell (via Selection or Range) Excel will assume you wish to work with the entire Worksheet of cells. For example, the 2 macros below would both select ALL blank cells on a Worksheet.

So, as you can see, specifying only a single cell Range can give unwanted results.

SpecialCells for Formulae & Constants

While we cannot specify more than one XlCellType (e.g. xlCellTypeConstants+xlCellTypeFormulas would fail) we can use the SpecialCells method to return only used cells housing numbers on a Worksheet (formulae & constants) and omit any cells containing text (formulae & constants).

You should take note of the On Error Resume Next statement in the above code. This is needed as when the SpecialCells Method condition cannot be met an error occurs. As you may, or may not know, a non valid Range Object returns the Nothing keyword. After Setting a Range variable to the SpecialCells Method we need to then check thatwe have been able to pass a Range Object to our Range variable. It is the If Statement (and 2 ElseIf) that checks this in the code above

Software Categories Search Software

Excel Add-ins || Excel Training || Excel Templates || Employee Scheduling Software || Excel Password Recovery and Access & Word etc|| Excel Recovery and Access & Word etc || Financial Software || Financial Calculators || Conversion Software || Construction Estimating Software || Drilling Software || Real Estate Investment Software || Time Management Software || Database Software || Neural Network Software || Trading Software || Charting Software || Windows & Internet Software || Barcodes Fonts, ActiveX, Labels and DLL's || Code Printing Software || Outlook Add-ins

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

OzGrid Excel and VBA Newsletter March 2006

Newsletter Index

EXCEL TIPS AND TRICKS

EXCEL VBA TIPS AND TRICKS