To get a count of the total number of cells in a range (empty and non-empty cells), use the following formula. This formula returns the number of cells in a range named Data. It simply multiplies the number of rows (returned by the ROWS function) by the number of columns (returned by the COLUMNS function).

=ROWS(Data)*COLUMNS(Data)

This formula will not work if the Data range consists of noncontiguous cells. In other words, Data must be a rectangular range of cells.

The following formula returns the number of blank (empty) cells in a range named Data:

=COUNTBLANK(Data)

The COUNTBLANK function also counts cells containing a formula that returns an empty string. For example, the formula that follows returns an empty string if the value in cell A1 is greater than 5. If the cell meets this condition, the COUNTBLANK function counts that cell.

=IF(A1>5,"",A1)

You can use the COUNTBLANK function with an argument that consists of entire rows or columns. For example, this next formula returns the number of blank cells in column A:

=COUNTBLANK(A:A)

The following formula returns the number of empty cells on the entire worksheet named Sheet1. You must enter this formula on a sheet other than Sheet1, or it will create a circular reference.

=COUNTBLANK(Sheet1!1:1048576)

To count nonblank cells, use the COUNTA function. The following formula uses the COUNTA function to return the number of nonblank cells in a range named Data:

=COUNTA(Data)

The COUNTA function counts cells that contain values, text, or logical values (TRUE or FALSE).

To count only the numeric cells in a range, use the following formula (which assumes the range is named Data):

=COUNT(Data)

Cells that contain a date or a time are considered to be numeric cells. Cells that contain a logical value (TRUE or FALSE) aren’t considered to be numeric cells.

To count the number of text cells in a range, you need to use an array formula. The array formula that follows returns the number of text cells in a range named Data:

{=SUM(IF(ISTEXT(Data),1))}

The following array formula uses Excel’s ISNONTEXT function, which returns TRUE if its argument refers to any nontext cell (including a blank cell). This formula returns the count of the number of cells not containing text (including blank cells):

{=SUM(IF(ISNONTEXT(Data),1))}

The following array formula returns the number of logical values (TRUE or FALSE) in a range named Data:

{=SUM(IF(ISLOGICAL(Data),1))}

Excel has three functions that help you determine whether a cell contains an error value:

You can use these functions in an array formula to count the number of error values in a range. The following array formula, for example, returns the total number of error values in a range named Data:

{=SUM(IF(ISERROR(data),1))}

Depending on your needs, you can use the ISERR or ISNA function in place of ISERROR.

If you would like to count specific types of errors, you can use the COUNTIF function. The following formula, for example, returns the number of #DIV/0! error values in the range named Data:

=COUNTIF(Data,"#DIV/0!")

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

Table 14.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 ?).

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 14.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 14.2. This worksheet demonstrates various counting techniques that use multiple criteria.

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

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

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

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

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

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

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

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

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

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

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

The MODE function returns the most frequently occurring value in a range. Figure 14.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 14.3. The MODE function returns the most frequently occurring value in a range.

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

The examples in this section demonstrate various ways to count the occurrences of a character or text string in a range of cells. Figure 14.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 14.4. This worksheet demonstrates various ways to count characters in a range.

=COUNTIF(Data,Text)

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

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

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

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

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

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

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

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

Figure 14.5. Creating a frequency distribution for the data in A1:E25.

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 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 14.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 14.6. Frequency distributions created by using the FREQUENCY function

Using formulas to create a frequency distribution

Figure 14.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 14.7. 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:

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

Note

The preceding formula uses the COUNTIFS function, which is new to Excel 2007. For compatibility with previous Excel versions, use this array formula:

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

Figure 14.8. 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 14.9 shows a frequency distribution (and chart) created with the Histogram option.

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

Note

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.

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 14.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 14.10. Using data bars within a pivot table to display a histogram.

Cross-Ref

I cover pivot tables in detail in Chapters 34 and 35, and you can learn more about the conditional formatting data bars in Chapter 21.

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 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 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 programmer, and you don’t need to install it.

It doesn’t get much simpler than this. The following formula returns the sum of all values in a range named Data:

=SUM(Data)

The SUM function can take up to 255 arguments. The following formula, for example, returns the sum of the values in five noncontiguous ranges:

=SUM(A1:A9,C1:C9,E1:E9,G1:G9,I1:I9)

You can use complete rows or columns as an argument for the SUM function. The formula that follows, for example, returns the sum of all values in column A. If this formula appears in a cell in column A, it generates a circular reference error.

=SUM(A:A)

The following formula returns the sum of all values on Sheet1 by using a range reference that consists of all rows. To avoid a circular reference error, this formula must appear on a sheet other than Sheet1.

=SUM(Sheet1!1:1048576)

The SUM function is very versatile. The arguments can be numerical values, cells, ranges, text representations of numbers (which are interpreted as values), logical values, and even embedded functions. For example, consider the following formula:

=SUM(B1,5,"6",,SQRT(4),A1:A5,TRUE)

This odd formula, which is perfectly valid, contains all of the following types of arguments, listed here in the order of their presentation:

You may want to display a cumulative sum of values in a range—sometimes known as a “running total.” Figure 14.11 illustrates a cumulative sum. Column B shows the monthly amounts, and column C displays the cumulative (year-to-date) totals.

Figure 14.11. Simple formulas in column C display a cumulative sum of the values in column B.

The formula in cell C2 is

=SUM(B$2:B2)

Notice that this formula uses a mixed reference— that is, the first cell in the range reference always refers to the same row (in this case, row 2). When this formula is copied down the column, the range argument adjusts such that the sum always starts with row 2 and ends with the current row. For example, after copying this formula down column C, the formula in cell C8 is

=SUM(B$2:B8)

You can use an IF function to hide the cumulative sums for rows in which data hasn’t been entered. The following formula, entered in cell C2 and copied down the column, is

=IF(B2<>"",SUM(B$2:B2),"")

Figure 14.12 shows this formula at work.

Figure 14.12. Using an IF function to hide cumulative sums for missing data.

In some situations, you may need to sum the n largest values in a range—for example, the top ten values. If your data resides in a table, you can use autofiltering to hide all but the top n rows and then display the sum of the visible data in the table’s total row.

Another approach is to sort the range in descending order and then use the SUM function with an argument consisting of the first n values in the sorted range.

A better solution—which doesn’t require a table or sorting—uses an array formula like this one:

{=SUM(LARGE(Data,{1,2,3,4,5,6,7,8,9,10}))}

This formula sums the ten largest values in a range named Data. To sum the ten smallest values, use the SMALL function instead of the LARGE function:

{=SUM(SMALL(Data,{1,2,3,4,5,6,7,8,9,10}))}

These formulas use an array constant comprised of the arguments for the LARGE or SMALL function. If the value of n for your top-n calculation is large, you may prefer to use the following variation. This formula returns the sum of the top 30 values in the Data range. You can, of course, substitute a different value for 30.

{=SUM(LARGE(Data,ROW(INDIRECT("1:30"))))}

The following formula returns the sum of the negative values in column F. In other words, it returns the total number of past-due days for all invoices. For this worksheet, the formula returns –63.

=SUMIF(Difference,"<0")

Because you omit the third argument, the second argument (“<0”) applies to the values in the Difference range.

You don’t need to hard-code the arguments for the SUMIF function into your formula. For example, you can create a formula, such as the following, which gets the criteria argument from the contents of cell G2:

=SUMIF(Difference,G2)

This formula returns a new result if you change the criteria in cell G2.

The following formula returns the sum of the past-due invoice amounts (in column C):

=SUMIF(Difference,"<0",Amount)

This formula uses the values in the Difference range to determine if the corresponding values in the Amount range contribute to the sum.

The following formula returns the total invoice amounts for the Oregon office:

=SUMIF(Office,"=Oregon",Amount)

Using the equal sign is optional. The following formula has the same result:

=SUMIF(Office,"Oregon",Amount)

To sum the invoice amounts for all offices except Oregon, use this formula:

=SUMIF(Office,"<>Oregon",Amount)

Let a Wizard Create Your Formula

Excel ships with an add-in called Conditional Sum Wizard. After you install this add-in, you can invoke the wizard by choosing Formulas Solutions Conditional Sum.

You can specify various conditions for your summing, and the add-in creates the formula for you (always an array formula). The Conditional Sum Wizard add-in, although a handy tool, is not all that versatile. For example, you can combine multiple criteria by using an And condition but not an Or condition.

To install the Conditional Sum Wizard add-in:

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 drop-down list labeled Manage.

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

5. Place a check mark next to Conditional Sum Wizard.

6. Click OK.

The following formula returns the total invoice amounts that have a due date after May 1, 2007:

=SUMIF(DateDue,">="&DATE(2007,5,1),Amount)

Notice that the second argument for the SUMIF function is an expression. The expression uses the DATE function, which returns a date. Also, the comparison operator, enclosed in quotes, is concatenated (using the & operator) with the result of the DATE function.

The formula that follows returns the total invoice amounts that have a future due date (including today):

=SUMIF(DateDue,">="&TODAY(),Amount)

Suppose that you want to get a sum of the invoice amounts that are past due and associated with the Oregon office. In other words, the value in the Amount range will be summed only if both of the following criteria are met:

If you’re using Excel 2007, the following formula does the job:

=SUMIFS(Amount,Difference,"<0",Office,"Oregon")

The array formula that follows returns the same result and will work in all versions of Excel.

{=SUM((Difference<0)*(Office="Oregon")*Amount)}

Suppose that you want to get a sum of past-due invoice amounts or ones associated with the Oregon office. In other words, the value in the Amount range will be summed if either of the following criteria is met:

This example requires an array formula:

{=SUM(IF((Office="Oregon")+(Difference<0),1,0)*Amount)}

A plus sign (+) joins the conditions; you can include more than two conditions.

As you may expect, things get a bit tricky when your criteria consists of both And and Or operations. For example, you may want to sum the values in the Amount range when both of the following conditions are met:

Notice that the second condition actually consists of two conditions joined with Or. The following array formula does the trick:

{=SUM((Difference<0)*IF((Office="Oregon")+
(Office="California"),1)*Amount)}