Counting Functions

Chapter 7 contains many formula examples to count cells based on various criteria. If you can't arrive at a formula-based solution for a counting problem, then you can probably create a custom function. This section contains three functions that perform counting.

On the CD 

The companion CD-ROM contains the workbook  counting functions.xlsm that demonstrates the functions in this section.

Counting Pattern-Matched Cells

The COUNTIF function accepts limited wildcard characters in its criteria: the question mark and the asterisk, to be specific. If you need more robust pattern matching, you can use the LIKE operator in a custom function.

 Function COUNTLIKE(rng As Range, pattern As String) As '   Count the cells in a range that match a pattern     Dim cell As Range     Dim cnt As Long     For Each cell In rng.Cells         If cell.Text Like pattern Then cnt = cnt + 1     Next cell     COUNTLIKE = cnt End Function 

The following formula counts the number of cells in G4:J15 that do not contain the letter e:

 =COUNTLIKE(G4:J15,"?[!e]*") 

Counting Sheets in a Workbook

The following COUNTSHEETS function accepts no arguments and returns the number of sheets in the workbook from where it's called:

 Function COUNTSHEETS() As Long     COUNTSHEETS = Application.Caller.Parent.Parent.Sheets.Count End Function 

This function uses Application.Caller to get the range where the formula was entered. Then it uses two Parent properties to go to the sheet and the workbook. Once at the workbook level, the Count property of the Sheets property is returned.

Counting Words in a Range

The WORDCOUNT function accepts a range argument and returns the number of words in that range:

 Function WORDCOUNT(rng As Range) As Long '   Count the words in a range of cells     Dim cell As Range     Dim WdCnt As Long     For Each cell In rng.Cells         If WorksheetFunction.IsText(cell.Value) Then             WdCnt = WdCnt + (Len(cell.Text) - _                 Len(Replace(cell.Text, " ", "")) + 1)         End If     Next cell     WORDCOUNT = WdCnt End Function 

Looping through the cells in the supplied range, the ISTEXT worksheet function is used to determine whether the cell has text. If it does, the number of spaces are counted and added to the total. Then one more space is added because a sentence with three spaces has four words. Spaces are counted by comparing the length of the text string with the length after the spaces have been removed with the VBA Replace function.

Counting Colors

The COUNTREDS function accepts a range argument and returns the number of cells whose font is red.

 Function COUNTREDS(rng As Range) As Long '   Count cells whose font color is red     Dim cell As Range     For Each cell In rng.Cells         If cell.Font.Color = vbRed Then COUNTREDS = COUNTREDS + 1     Next cell End Function 

The Color property of each cell's Font object is compared with vbRed, which is a built-in constant whose intrinsic value is the same as Excel's value for the color red. This function is very specialized. However, a more general function-one in which the color to be counted is supplied as an argument-could be written.

Note 

Although this section deals with counting, many of the functions can easily be converted into summing functions. The COUNTREDS function, for example, could be changed to SUMREDS with only a slight change to the loop:

     SUMREDS = SUMREDS + cell.Value