Advanced Counting Formulas

Most of the basic examples I presented previously 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 7-2 contains 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 ?).

Table 7-2: EXAMPLES OF FORMULAS USING THE COUNTIF FUNCTION
Open table as spreadsheet

=COUNTIF(Data,12)	Returns the number of cells containing the value 12
=COUNTIF(Data,"<0")	Returns the number of cells containing a negative value
=COUNTIF(Data,"<>0")	Returns the number of cells not equal to 0
=COUNTIF(Data,">5")	Returns the number of cells greater than 5
=COUNTIF(Data,A1)	Returns the number of cells equal to the contents of cell A1
=COUNTIF(Data,">"&A1)	Returns the number of cells greater than the value in cell A1
=COUNTIF(Data,"*")	Returns the number of cells containing text
=COUNTIF(Data,"???") characters	Returns the number of text cells containing exactly three
=COUNTIF(Data,"budget")	Returns the number of cells containing the single word budget and nothing else (not case sensitive)
=COUNTIF(Data,"*budget*")	Returns the number of cells containing the text budget anywhere within the text
=COUNTIF(Data,"A*")	Returns the number of cells containing text that begins with the letter A (not case sensitive)
=COUNTIF(Data,TODAY())	Returns the number of cells containing the current date
=COUNTIF(Data,">" &AVERAGE(Data))	Returns the number of cells with a value greater than the average
=COUNTIF(Data,">" &AVERAGE(Data)+ STDEV(Data)*3)	Returns the number of values exceeding three standard deviations above the mean
=COUNTIF(Data,3)+COUNTIF) (Data,-3	Returns the number of cells containing the value 3 or −3
=COUNTIF(Data,TRUE)	Returns the number of cells containing logical TRUE
=COUNTIF(Data,TRUE)+ COUNTIF(Data,FALSE)	Returns the number of cells containing a logical value (TRUE or FALSE)
=COUNTIF(Data,"#N/A")	Returns the number of cells containing the #N/A error value

Counting Cells That Meet 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 7-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.

Figure 7-2: This worksheet demonstrates various counting techniques that use multiple criteria.

On the CD 

The workbook  multiple criteria counting.xlsx is available on the companion CD-ROM.

New 

Several of the examples in this section use the COUNTIFS function, which is new to Excel 2007. I also present alternative version of the formulas, which should be used if you plan to share your workbook with others who don't use Excel 2007.

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 0 and less than or equal to 12. Any cell that has a positive value less than or equal to 12 will be included in the count. For this example, the COUNTIFS function will do the job:

 =COUNTIFS(Amount,">0",Amount,"<=12") 

Note 

If the data is contained in a table, you can use the new Excel 2007 method of referencing data within a table. For example, if the table is named Table1, the preceding formula can be rewritten as

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

This method of writing formulas does not require named ranges.

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 example above 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(Data,">0")-COUNTIF(Data,">12") 

This formula counts the number of values that are greater than 0 and then subtracts the number of values that are greater than 12. The result is the number of cells that contain a value greater than 0 and less than or equal to 12.

Creating this type of formula can be confusing because the formula refers to a condition ">12" even though the goal is to count values that are less than or equal to 12. An alternate technique is to use an array formula, such as the one that follows. You may find creating this type of formula easier.

 {=SUM((Data>0)*(Data<=12))} 

Note 

When you enter an array formula, remember to use Ctrl+Shift+Enter-and don't type the brackets.

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 1,000.

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 version of Excel, uses the SUMPRODUCT function. The formula below 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 using an Or criterion, you can sometimes use multiple COUNTIF functions. The following formula, for example, counts the number of 1s, 3s, and 5s in the range named Data:

 =COUNTIF(Data,1)+COUNTIF(Data,3)+COUNTIF(Data,5) 

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(Data,{1,3,5}))} 

But if you base your Or criteria on cells other than the cells being counted, the COUNTIF function won't work. Refer to Figure 7-2. Suppose 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 1,000.

The following array formula returns the correct count:

 {=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.

You can add two COUNTIFS functions to get the desired result:

 =COUNTIFS(Month,"January",SalesRep,"Brooks")+ COUNTIFS(Month,"January",SalesRep,"Cook") 

Because you have to repeat the AND portion of the criteria in each function's arguments, using COUNTIFS can produce long formulas with more criteria. When you a lot of criteria, it makes sense to use an array formula, like this one that produces the same result:

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

Counting the Most Frequently Occurring Entry

Excel's MODE function returns the most frequently occurring value in a range. Figure 7-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) 

Figure 7-3: The MODE function returns the most frequently occurring value in a range.

The formula returns an #N/A error if the Data range contains no duplicated values.

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 3 because the modal value (10) appears three 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))} 

Caution 

If there is more than one most-frequent value, the preceding formula will return only the first in the list.

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 7-4 shows a worksheet used for these examples. Various text appears in the range A1:A10 (named Data); cell B1 is named Text.

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

On the CD 

The companion CD-ROM contains a workbook named  counting text in a range.xlsx that demonstrates the formulas in this section.

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

An alternative is a longer array formula that uses the SEARCH function:

 {=SUM(IF(NOT(ISERROR(SEARCH(text,data))),1))} 

The SEARCH function returns an error if Text is not found in Data. The preceding formula counts one for every cell where SEARCH does not find an error. Because SEARCH is not case sensitive, neither is this formula.

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

Like the SEARCH function, the FIND function will return an error if Text is not found in Data, as in this alternative array formula:

 {=SUM(IF(NOT(ISERROR(FIND(text,data))),1))} 

Unlike SEARCH, the FIND function is case sensitive.

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

To understand how this formula works, you need a basic understanding of array formulas. (See Chapter 14 for an introduction to this topic.) In Figure 7-5, range A1:A12 is named Data. Range C1:C12 contains the following multicell array formula. A single formula was entered into all 12 cells in the range.

Figure 7-5: Using an array formula to count the number of unique values in a range.

 {=COUNTIF(Data,Data)} 

On the CD 

You can access the workbook  count unique.xlsx shown in Figure 7-5 on the companion CD-ROM.

The array in range C1:C12 consists of the count of each value in Data. For example, the number 100 appears three times, so each array element that corresponds to a value of 100 in the Data range has a value of 3.

Range D1:D12 contains the following array formula:

 {=1/C1:C12} 

This array consists of each value in the array in range C1:C12, divided into 1. For example, each cell in the original Data range that contains a 200 has a value of 0.5 in the corresponding cell in D1:D12.

Summing the range D1:D12 gives the number of unique items in Data. The array formula presented at the beginning of this section essentially creates the array that occupies D1:D12 and sums the values.

This formula has 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)))} 

Cross Ref 

To create an array formula that returns a list of unique items in a range, refer to Chapter 15.

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 As, Bs, Cs, and so on. Excel provides a number of ways to create frequency distributions. You can

• Use the FREQUENCY function.

• Create your own formulas.

• Use the Analysis ToolPak add-in.

On the CD 

The  frequency distribution.xlsx workbook that demonstrates these three techniques appears on the companion CD-ROM.

Cross Ref 

If your data is in the form of a worksheet database or table, you can also use a pivot table to create a frequency distribution. Refer to Chapter 18 for more information about pivot tables.

THE FREQUENCY FUNCTION

Using Excel's FREQUENCY function presents the easiest way to create a frequency distribution. This function always returns an array, so you must use it in an array formula entered into a multicell range.

Figure 7-6 shows some data in range A1:E20 (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 1–50, 51–100, 101–150, and so on. See the sidebar, "Creating Bins for a Frequency Distribution" to discover an easy way to create a bin range.

Figure 7-6: Creating a frequency distribution for the data in A1:E20.

To create the frequency distribution, select a range of cells that correspond to the number of cells in the bin range. Then enter the following array formula:

 {=FREQUENCY(Data,G2:G11)} 

The array formula enters 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 7-7 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 7-7: Frequency distributions created using the FREQUENCY function.

Creating Bins for a Frequency Distribution

When creating a frequency distribution, you must first enter the values into the bin range. The number of bins determines the number of categories in the distribution. Most of the time, each of these bins will represent an equal range of values.

To create ten evenly spaced bins for values in a range named Data, enter the following array formula into a range of ten cells in a column:

 {=MIN(Data)+(ROW(INDIRECT("1:10"))* (MAX(Data)-MIN(Data)+1)/10)-1} 

This formula creates ten bins, based on the values in the Data range. The upper bin will always equal the maximum value in the range.

To create more or fewer bins, use a value other than 10 and enter the array formula into a range that contains the same number of cells. For example, to create five bins, enter the following array formula into a five-cell vertical range:

 {=MIN(Data)+(ROW(INDIRECT("1:5"))*(MAX(Data)-MIN(Data)+1)/5)-1} 

USING FORMULAS TO CREATE A FREQUENCY DISTRIBUTION

Figure 7-8 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) qualifies for a B.

Figure 7-8: Creating a frequency distribution of test scores.

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

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

You may recognize this formula from a previous section in this chapter. (See "Counting Cells That Meet Multiple Criteria.") This formula was copied to the four cells below G2.

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

After you install the Analysis ToolPak add-in, you can use the Histogram option to create a frequency distribution. Start by entering your bin values in a range. Then choose Data Analysis Data Analysis to display the Data Analysis dialog box. Next, select Histogram and click OK. You should see the Histogram dialog box shown in Figure 7-9.

Figure 7-9: The Analysis ToolPak's Histogram dialog box.

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

Figure 7-10: A frequency distribution and chart generated by the Analysis ToolPak's Histogram option.

Caution 

Note that the frequency distribution consists of values, not formulas. Therefore, if you make any changes to your input data, you need to rerun the Histogram procedure to update the results.

Is the Analysis ToolPak Installed?

To make sure that the Analysis ToolPak add-in is installed, choose Add-Ins Menu Commands. If Data Analysis appears in the group, you're all set. If not, you need to install it:

1. Choose Office Excel Options to display the Excel Options dialog box.

2. Click the Add-ins tab on the left.

3. Select Excel Add-Ins from the Manage drop-down list.

4. Click Go to display the Add-Ins dialog box.

5. Place a check mark next to Analysis ToolPak.

6. Click OK.

USING ADJUSTABLE BINS TO CREATE A HISTOGRAM

Figure 7-11 shows a worksheet with student grades listed in column B (67 students total). Columns D and E contain formulas that calculate the upper and lower limits for bins, based on the entry in cell E1 (named BinSize). For example, if BinSize is 10 (as in the figure), then each bin contains ten scores (1–10, 11–20, and so on).

Figure 7-11: The chart displays a histogram; the contents of cell E1 determine the number of categories.

On the CD 

The workbook  adjustable bins.xlsx, shown in Figure 7-11, is available on the companion CD-ROM.

The chart uses two dynamic names in its SERIES formula. You can define the name Categories with the following formula:

 =OFFSET(Sheet1!$E$4,0,0,ROUNDUP(100/BinSize,0)) 

You can define the name Frequencies with this formula:

 =OFFSET(Sheet1!$F$4,0,0,ROUNDUP(100/BinSize,0)) 

The net effect is that the chart adjusts automatically when you change the BinSize cell.

Cross Ref 

See Chapter 17 for more about creating a chart that uses dynamic names in its SERIES formula.