Back to Excel Newsletter Archives

EXCEL TIPS AND TRICKS | EXCEL VBA TIPS AND TRICKS

**Current Special!** Complete Excel __Excel Training Course__ for Excel 97 - Excel 2003, only ~~$145.00~~. $59.95 Instant Buy/Download

**Excel Sum Wizard**

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 cellCopy the formula and conditional values**

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

Click the

Select cell G2 and click

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

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

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 –

Under Is: -

Under This Value –

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

Now to add the second condition:

Under column –

Under Is: -

Under This Value –

Again select

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;

In Step 4 of the Wizard you are asked to nominate a cell in which to paste your formula. Nominate cell G4 and click

**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 .**

**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.

ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)

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

ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas,xlNumbers)

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;

Sub ColorAllFormulae()ActiveSheet.UsedRange.SpecialCells _ (xlCellTypeFormulas).Interior.ColorIndex = 6End SubSub NegativeAllNumberFormulae()Dim rRange As Range, rCell As Range Set rRange = ActiveSheet.UsedRange.SpecialCells _ (xlCellTypeFormulas, xlNumbers) For Each rCell In rRange rCell = rCell.Value * -1 Next rCellEnd Sub

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;

Sub NegativeAllNumberFormulae2()With Range("IV65536") .Value = -1 .Copy ActiveSheet.UsedRange.SpecialCells _ (xlCellTypeFormulas, xlNumbers).PasteSpecial _ xlPasteValues, xlPasteSpecialOperationMultiply .Clear End WithEnd Sub

**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.

Sub SelectAllBlanks()ActiveSheet.UsedRange.SpecialCells _ (xlCellTypeBlanks).SelectEnd SubSub SelectAllBlanks2()Range("A1").SpecialCells _ (xlCellTypeBlanks).SelectEnd Sub

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).

Sub AllNummericCells()Dim rCcells As Range, rFcells As RangeDim rAcells As Range 'Set variable to all used cells Set rAcells = ActiveSheet.UsedRange On Error Resume Next 'In case of no numeric formula or constants. 'Set variable to all numeric constants Set rCcells = rAcells.SpecialCells(xlCellTypeConstants, xlNumbers) 'Set variable to all numeric formulas Set rFcells = rAcells.SpecialCells(xlCellTypeFormulas, xlNumbers) 'Determine which type of numeric data (formulas, constants or none) If rCcells Is Nothing And rFcells Is Nothing Then MsgBox "You Worksheet contains no numbers" End ElseIf rCcells Is Nothing Then Set rAcells = rFcells 'formulas ElseIf rFcells Is Nothing Then Set rAcells = rCcells 'constants Else Set rAcells = Application.Union(rFcells, rCcells) 'Both End If On Error GoTo 0 rAcells.SelectEnd Sub

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

**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.