Advanced Counting Formulas

Most of the basic examples I presented earlier in this chapter use functions or formulas that perform conditional counting. The advanced counting formulas that I present here represent more complex examples for counting worksheet cells, based on various types of criteria.

Counting cells by using the COUNTIF function

Excel’s COUNTIF function is useful for single-criterion counting formulas. The COUNTIF function takes two arguments:

• range: The range that contains the values that determine whether to include a particular cell in the count

• criteria: The logical criteria that determine whether to include a particular cell in the count

Table 17-2 lists several examples of formulas that use the COUNTIF function. These formulas all work with a range named Data. As you can see, the criteria argument proves quite flexible. You can use constants, expressions, functions, cell references, and even wildcard characters (* and ?).

Counting cells by using multiple criteria

In many cases, your counting formula will need to count cells only if two or more criteria are met. These criteria can be based on the cells that are being counted or based on a range of corresponding cells.

Figure 17-2 shows a simple worksheet that I use for the examples in this section. This sheet shows sales data categorized by Month, SalesRep, and Type. The worksheet contains named ranges that correspond to the labels in row 1.

Using And criteria

An And criterion counts cells if all specified conditions are met. A common example is a formula that counts the number of values that fall within a numerical range. For example, you may want to count cells that contain a value greater than 100 and less than or equal to 200. For this example, the new COUNTIFS function will do the job:

=COUNTIFS(Amount,">100",Amount,"<=200")

=COUNTIFS(Table1[Amount],">100",Table1[Amount],"<=200")

The COUNTIFS function accepts any number of paired arguments. The first member of the pair is the range to be counted (in this case, the range named Amount); the second member of the pair is the criterion. The preceding example contains two sets of paired arguments and returns the number of cells in which Amount is greater than 100 and less than or equal to 200.

Prior to Excel 2007, you would need to use a formula like this:

=COUNTIF(Amount,">100")-COUNTIF(Amount,">200")

The formula counts the number of values that are great than 100 and then subtracts the number of values that are greater than or equal to 200. The result is the number of cells that contain a value greater than 100 and less than or equal to 200. This formula can be confusing because the formula refers to a condition ">200" even though the goal is to count values that are less than or equal to 200. Yet another alternate technique is to use an array formula, like the one that follows. You may find it easier to create this type of formula:

{=SUM((Amount>100)*(Amount<=200))}

Sometimes, the counting criteria will be based on cells other than the cells being counted. You may, for example, want to count the number of sales that meet the following criteria:

• Month is January, and

• SalesRep is Brooks, and

• Amount is greater than 1000

The following formula (for Excel 2007 only) returns the number of items that meets all three criteria. Note that the COUNTIFS function uses three sets of pairs of arguments.

=COUNTIFS(Month,"January",SalesRep,"Brooks",Amount,">1000")

An alternative formula, which works with all versions of Excel, uses the SUMPRODUCT function. The following formula returns the same result as the previous formula:

=SUMPRODUCT((Month="January")*(SalesRep="Brooks")*(Amount>1000))

Yet another way to perform this count is to use an array formula:

{=SUM((Month="January")*(SalesRep="Brooks")*(Amount>1000))}

Using Or criteria

To count cells by using an Or criterion, you can sometimes use multiple COUNTIF functions. The following formula, for example, counts the number of sales made in January or February:

=COUNTIF(Month,"January")+COUNTIF(Month,"February")

You can also use the COUNTIF function in an array formula. The following array formula, for example, returns the same result as the previous formula:

{=SUM(COUNTIF(Month,{"January","February"}))}

But if you base your Or criteria on cells other than the cells being counted, the COUNTIF function won’t work. (Refer to Figure 17-2.) Suppose that you want to count the number of sales that meet the following criteria:

• Month is January, or

• SalesRep is Brooks, or

• Amount is greater than 1000

If you attempt to create a formula that uses COUNTIF, some double counting will occur. The solution is to use an array formula like this:

{=SUM(IF((Month="January")+(SalesRep="Brooks")+(Amount>1000),1))}

Combining And and Or criteria

In some cases, you may need to combine And and Or criteria when counting. For example, perhaps you want to count sales that meet the following criteria:

• Month is January, and

• SalesRep is Brooks, or SalesRep is Cook

This array formula returns the number of sales that meet the criteria:

{=SUM((Month="January")*IF((SalesRep="Brooks")+
(SalesRep="Cook"),1))}

Counting the most frequently occurring entry

The MODE function returns the most frequently occurring value in a range. Figure 17-3 shows a worksheet with values in range A1:A10 (named Data). The formula that follows returns 10 because that value appears most frequently in the Data range:

=MODE(Data)

To count the number of times the most frequently occurring value appears in the range (in other words, the frequency of the mode), use the following formula:

=COUNTIF(Data,MODE(Data))

This formula returns 4, because the modal value (10) appears four times in the Data range.

The MODE function works only for numeric values. It simply ignores cells that contain text. To find the most frequently occurring text entry in a range, you need to use an array formula.

To count the number of times the most frequently occurring item (text or values) appears in a range named Data, use the following array formula:

{=MAX(COUNTIF(Data,Data))}

This next array formula operates like the MODE function, except that it works with both text and values:

{=INDEX(Data,MATCH(MAX(COUNTIF(Data,Data)),COUNTIF(Data,Data),0))}

Counting the occurrences of specific text

The examples in this section demonstrate various ways to count the occurrences of a character or text string in a range of cells. Figure 17-4 shows a worksheet used for these examples. Various text strings appear in the range A1:A10 (named Data); cell B1 is named Text.

Figure 17-4. This worksheet demonstrates various ways to count characters in a range.

[View full size image]

Entire cell contents

To count the number of cells containing the contents of the Text cell (and nothing else), you can use the COUNTIF function as the following formula demonstrates.

=COUNTIF(Data,Text)

For example, if the Text cell contains the string “Alpha” the formula returns 2 because two cells in the Data range contain this text. This formula is not case sensitive, so it counts both “Alpha” (cell A2) and “alpha” (cell A10). Note, however, that it does not count the cell that contains “Alpha Beta” (cell A8).

The following array formula is similar to the preceding formula, but this one is case sensitive:

{=SUM(IF(EXACT(Data,Text),1))}

Partial cell contents

To count the number of cells that contain a string that includes the contents of the Text cell, use this formula:

=COUNTIF(Data,"*"&Text&"*")

For example, if the Text cell contains the text “Alpha” the formula returns 3 because three cells in the Data range contain the text “alpha” (cells A2, A8, and A10). Note that the comparison is not case sensitive.

If you need a case-sensitive count, you can use the following array formula:

{=SUM(IF(LEN(Data)-LEN(SUBSTITUTE(Data,Text,""))>0,1))}

If the Text cells contain the text “Alpha” the preceding formula returns 2 because the string appears in two cells (A2 and A8).

Total occurrences in a range

To count the total number of occurrences of a string within a range of cells, use the following array formula:

{=(SUM(LEN(Data))-SUM(LEN(SUBSTITUTE(Data,Text,""))))/
LEN(Text)}

If the Text cell contains the character “B” the formula returns 7 because the range contains seven instances of the string. This formula is case sensitive.

The following array formula is a modified version that is not case sensitive:

{=(SUM(LEN(Data))-SUM(LEN(SUBSTITUTE(UPPER(Data),
UPPER(Text),""))))/LEN(Text)}

Counting the number of unique values

The following array formula returns the number of unique values in a range named Data:

{=SUM(1/COUNTIF(Data,Data))}

Useful as it is, this formula does have a serious limitation: If the range contains any blank cells it returns an error. The following array formula solves this problem:

{=SUM(IF(COUNTIF(Data,Data)=0,"",1/COUNTIF(Data,Data)))}

Creating a frequency distribution

A frequency distribution basically comprises a summary table that shows the frequency of each value in a range. For example, an instructor may create a frequency distribution of test scores. The table would show the count of A’s, B’s, C’s, and so on. Excel provides a number of ways to create frequency distributions. You can

• Use the FREQUENCY function

• Use the Analysis ToolPak add-in

• Create your own formulas

• Use a pivot table

The FREQUENCY function

Using the FREQUENCY function to create a frequency distribution can be a bit tricky. This function always returns an array, so you must use it in an array formula that’s entered into a multicell range.

Figure 17-5 shows some data in range A1:E25 (named Data). These values range from 1 to 500. The range G2:G11 contains the bins used for the frequency distribution. Each cell in this bin range contains the upper limit for the bin. In this case, the bins consist of <=50, 51–100, 101–150, and so on.

To create the frequency distribution, select a range of cells that corresponds to the number of cells in the bin range (in this example, H2:H11). Then enter the following array formula (press Ctrl+Shift+Enter to enter it):

{=FREQUENCY(Data,G2:G11)}

The array formula returns the count of values in the Data range that fall into each bin. To create a frequency distribution that consists of percentages, use the following array formula:

{=FREQUENCY(Data,G2:G11)/COUNT(Data)}

Figure 17-6 shows two frequency distributions—one in terms of counts and one in terms of percentages. The figure also shows a chart (histogram) created from the frequency distribution.

Figure 17-6. Frequency distributions created by using the FREQUENCY function.

[View full size image]

Using formulas to create a frequency distribution

Figure 17-7 shows a worksheet that contains test scores for 50 students in column B (the range is named Grades). Formulas in columns G and H calculate a frequency distribution for letter grades. The minimum and maximum values for each letter grade appear in columns D and E. For example, a test score between 80 and 89 (inclusive) earns a B. In addition, a chart displays the distribution of the test scores.

Figure 17-7. Creating a frequency distribution of test scores.

[View full size image]

The formula in cell G2 that follows is an array formula that counts the number of scores that qualify for an A:

=COUNTIFS(Grades,">="&D2,Grades,"<="&E2)

You may recognize this formula from a previous section in this chapter (see “Counting cells by using multiple criteria”). This formula was copied to the four cells below G2.

{=SUM((Grades>=D2)*(Grades<=E2))}

The formulas in column H calculate the percentage of scores for each letter grade. The formula in H2, which was copied to the four cells below H2, is

=G2/SUM($G$2:$G$6)

Using the Analysis ToolPak to create a frequency distribution

The Analysis ToolPak add-in, distributed with Excel, provides another way to calculate a frequency distribution. Start by entering your bin values in a range. Then choose Data Analysis Analysis to display the Data Analysis dialog box. If this command is not available, see the sidebar, “Is the Analysis Toolpak Installed?”.

In the Data Analysis dialog box, select Histogram and click OK. You should see the Histogram dialog box shown in Figure 17-8.

Specify the ranges for your data (Input Range), bins (Bin Range), and results (Output Range), and then select any options. Figure 17-9 shows a frequency distribution (and chart) created with the Histogram option.

Figure 17-9. A frequency distribution and chart generated by the Analysis ToolPak’s Histogram option.

[View full size image]

Is the Analysis Toolpak Installed? To make sure that the Analysis Toolpak add-in is installed, Click the Data tab. If the Ribbon displays the Data Analysis command in the Analysis group, you’re all set. If not, you’ll need to install the add-in: 1. Choose Office Button Excel Options to display the Excel Options dialog box. 2. Click the Add-ins choice at the left. 3. Select Excel Add-Ins from the drop-down labeled Manage. 4. Click Go to display the Add-Ins dialog box. 5. Place a check mark next to Analysis ToolPak. 6. Click OK. Note: In the Add-Ins dialog box, you see an additional add-in, Analysis ToolPak - VBA. This add-in is for programmers, and you don’t need to install it.

Using a pivot table to create a frequency distribution

If your data is in the form of a table, you may prefer to use a pivot table to create a histogram. Figure 17-10 shows the student grade data summarized in a pivot table. The data bars were added using the new conditional formatting features in Excel 2007.

Figure 17-10. Using data bars within a pivot table to display a histogram.

[View full size image]