Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)

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.

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.