Chapter 34: Summing Cells by Reference to a Master Cell

Overview

As you saw in early chapters of this book, you can create your own functions to use as formulas within the spreadsheet, just as if Microsoft had written them instead of you. This chapter shows how to create a more complicated function. When users are summing a row or column of cells, they sometimes want to include only particular values in the sum. These values are typically based on some attribute of the cell such as a font property or a background color .

Occasionally, people ask me, ‚“Can I just add up the numbers in italics or in bold? ‚½ I have to tell them that there is no built-in way to do this in Excel. However, with the flexibility of VBA, you can write your own formula to do this. The syntax on your spreadsheet is as follows :

SUMCELLSBYREF ( range , reference , attribute )

• The range is a range of cells defined in the same way as the standard SUM function. Select this by entering the formula and then dragging the cursor over your range of cells.

• The reference is a single cell reference set to the attributes you wish to sum, such as those in italic.

• The attribute is a string containing one of the following:

Attribute	Description
Color	Sums all cells with the same font color as in the reference cell
Italic	Sums all cells set to the same italic font setting as the reference cell
Bold	Sums all cells set to the same bold font setting as the reference cell
Size	Sums all cells set to the same font size as the reference cell
Underline	Sums all cells set to the same underline setting as the reference cell
Subscript	Sums all cells set to the same subscript setting as the reference cell
Superscript	Sums all cells set to the same superscript setting as the reference cell

The code to do all of this is shown here. Place the following into a module:

 Public Function SUMCELLSBYREF(cells_to_sum As Object, r As Object, p _ 
 As String) 
 Application.Volatile 
 total = 0 
 For Each cell In cells_to_sum 
 
 If p = "bold" And cell.Font.Bold = r.Font.Bold Then 
 
 total = total + cell.Value 
 
 End If 
 
 
 If p = "color" And cell.Font.Color = r.Font.Color Then 
 total = total + cell.Value 
 
 End If 
 
 If p = "italic" And cell.Font.Italic = r.Font.Italic Then 
 
 total = total + cell.Value 
 
 End If 
 If p = "name" And cell.Font.Name = r.Font.Name Then 
 
 total = total + cell.Value 
 
 End If 
 
 If p = "size" And cell.Font.Size = r.Font.Size Then 
 
 total = total + cell.Value 
 End If 
 
 If p = "underline" And cell.Font.Underline = r.Font.Underline Then 
 
 total = total + cell.Value 
 
 End If 
 
 If p = "subscript" And cell.Font.Subscript = r.Font.Subscript Then 
 total = total + cell.Value 
 
 End If 
 If p = "superscript" And cell.Font.Superscript = r.Font.Superscript 
 Then 
 
 total = total + cell.Value 
 
 End If 
 Next cell 
 SUMCELLSBYREF = total 
 End Function 

This function has to be declared as a Public function for it to be available within the spreadsheet. As per the syntax, three parameters are passed over:

Name	Type	Description
cells_to_sum	Object	Range address of cells to sum
r	Object	Address of reference cell
p	String	Attribute type; for example, the string ‚“color2 ‚½ or the string ‚“font size ‚½

The statement Application.Volatile allows a range of cells to be selected dynamically from within the formula. It works in the same way as the SUM function in that you can drag the cursor over the range while within the formula. A variable called total , which will hold the overall value of the sum, is set to 0. Using a For Each..Next loop, each cell within the cells_to_sum object is cycled through.

There are condition statements set up for each of the values that can be held by p , the attribute statement. For example, if p states " bold ", the code tests the condition that the cell bold attribute equals the reference cell bold attribute. This could be true or false depending on what you are trying to achieve in your summation. If the attributes agree, the variable total is increased by the cell value. The total variable contains a running total as the cells are processed .

When all the cells have been examined, the variable SUMCELLSBYREF is given the value of total , and this is the returned value to the spreadsheet.

To try this out, you do not need to run the code ‚ just enter a formula as you normally would within Excel:

 =SUMCELLSBYREF(A1..A4,C1,"bold") 

You will find that if you click the Formula Paste icon on the Formula toolbar, the formula will appear under the User Defined Formula section, and you can use it as you would any other formula. If you miss any parameters, you will get the standard Excel errors.

Try entering some data and then put the formula in. Bear in mind that if you have Recalc set to manual, it will not recalculate the result automatically. A sample result is shown in Figure 34-1.

Figure 34-1: Example of SUMCELLSBYREF in action