IN THIS CHAPTER
Many of the most common spreadsheet questions involve counting and summing values and other worksheet elements. It seems that people are always looking for formulas to count or to sum various items in a worksheet. If I've done my job, this chapter answers the majority of such questions. It contains many examples that you can easily adapt to your own situation.
Generally, a counting formula returns the number of cells in a specified range that meet certain criteria. A summing formula returns the sum of the values of the cells in a range that meet certain criteria.
Table 13.1 lists the Excel worksheet functions that come into play when creating counting and summing formulas. Not all these functions are covered in this chapter. If none of the functions in Table 13.1 can solve your problem, it's likely that an array formula can come to the rescue.
Table 13.1 Excel Counting and Summing Functions
Function | Description |
COUNT |
Returns the number of cells that contain a numeric value. |
COUNTA |
Returns the number of nonblank cells. |
COUNTBLANK |
Returns the number of blank cells. |
COUNTIF |
Returns the number of cells that meet a specified criterion. |
COUNTIFS * |
Returns the number of cells that meet multiple criteria. |
DCOUNT |
Counts the number of records that meet specified criteria; used with a worksheet database. |
DCOUNTA |
Counts the number of nonblank records that meet specified criteria; used with a worksheet database. |
DSUM |
Returns the sum of a column of values that meet specified criteria; used with a worksheet database. |
FREQUENCY |
Calculates how often values occur within a range of values and returns a vertical array of numbers. Used only in a multicell array formula. |
SUBTOTAL |
When used with a first argument of 2 , 3 , 102 , or 103 , returns a count of cells that comprise a subtotal; when used with a first argument of 9 or 109 , returns the sum of cells that comprise a subtotal. |
SUM |
Returns the sum of its arguments. |
SUMIF |
Returns the sum of cells that meet a specified criterion. |
SUMIFS * |
Returns the sum of cells that meet multiple criteria. |
SUMPRODUCT |
Multiplies corresponding cells in two or more ranges and returns the sum of those products. |
* These functions were introduced in Excel 2007.
All the basic counting formulas presented in this section are straightforward and relatively simple. They demonstrate the capability of the Excel counting functions to count the number of cells in a range that meet specific criteria.
Figure 13.1 shows a worksheet that uses formulas (in column E) to summarize the contents of range A1:B10 — a 20-cell range named Data. This range contains a variety of information, including values, text, logical values, errors, and empty cells.
Oddly, Excel doesn't have a function that simply counts the number of cells in a range reference. To get a count of the total number of cells in a range (empty and nonempty 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)
This function works only with a contiguous range of cells. If Data is defined as a noncontiguous range, the function returns a #VALUE!
error.
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, the following 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 the Excel 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:
ISERROR
: Returns TRUE
if the cell contains any error value (#N/A
, #VALUE!
, #REF!
, #DIV/0!
, #NUM!
, #NAME?
, or #NULL!
)ISERR
: Returns TRUE
if the cell contains any error value except #N/A
ISNA
: Returns TRUE
if the cell contains the #N/A
error valueYou 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 want 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!")
Note that the COUNTIF
functions works only with a contiguous range argument. If Data is a noncontiguous range, the formula returns a #VALUE!
error.
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 in this section represent more complex examples for counting worksheet cells, based on various types of criteria.
The COUNTIF
function, which is useful for single-criterion counting formulas, takes two arguments:
range
: The range that contains the values that determine whether to include a particular cell in the countcriteria
: The logical criteria that determine whether to include a particular cell in the countTable 13.2 lists several examples of formulas that use the COUNTIF
function. All these formulas 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 13.2 Examples of Formulas Using the COUNTIF Function
=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,"???") |
Returns the number of text cells containing exactly three characters |
=COUNTIF(Data,"budget") |
Returns the number of cells containing the single word budget (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 of the values |
=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 the logical value 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 |
Note that the COUNTIF
functions work only with a contiguous range argument. If Data is defined as a noncontiguous range, the formula returns a #VALUE!
error.
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 on a range of corresponding cells.
Figure 13.2 shows a simple worksheet that I use for the examples in this section. This sheet shows sales data categorized by Month, Sales Rep, and Type. The worksheet contains four named ranges that correspond to the labels in row 1.
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 that's greater than 100 and less than or equal to 200. For this example, the COUNTIFS
function will do the job:
=COUNTIFS(Amount,">100",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")
This formula counts the number of values that are greater 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 all the following criteria:
The following formula (for Excel 2007 and later) returns the number of items that meet all three criteria. Note that the COUNTIFS
function uses three sets of paired 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))}
An Or criterion counts cells if any of the multiple conditions is met. 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 13.2). Suppose that you want to count the number of sales that meet at least one of the following criteria:
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))}
In some cases, you may need to combine And criteria and Or criteria when counting. For example, perhaps you want to count sales that meet both of the following criteria:
This array formula returns the number of sales that meet the criteria:
{=SUM((Month="January")*IF((SalesRep="Brooks")+(SalesRep="Cook"),1))}
The MODE
function returns the most frequently occurring value in a range. In case of a tie, it returns the mode of the value that appears first in the range. Figure 13.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 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))}
The examples in this section demonstrate various ways to count the occurrences of a character or text string in a range of cells. Figure 13.4 shows a worksheet used for these examples. Various text strings appear in the range A1:A10 (named Data); cell B1 is named Text.
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))}
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).
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)}
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 is a summary table that shows the frequency of each value in a range. For example, an instructor may create a frequency distribution of grades. 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
FREQUENCY
function.Excel 2016 includes a new Histogram chart feature, which can create a frequency histogram from a set of values. This feature does not create a table, however. For an example of a histogram chart, see Chapter 19, “Getting Started Making Charts.”
Using the FREQUENCY
function to create a frequency distribution can be a bit tricky; it is probably the most difficult way to create a frequency distribution. The FREQUENCY
function always returns an array, so you must use it in an array formula that's entered into a multicell range.
Figure 13.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. The goal is to count the number of values that fall into each bin.
To create the frequency distribution, select a range of cells that corresponds to the number of cells in the bin range. (In this example, select H2:H11 because the bins are in G2:G11.) Then enter the following array formula into the selected range. (Press Ctrl+Shift+Enter to enter an array formula.)
{=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 13.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 13.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.
The formula in cell G2 that follows 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 based on 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)
The Analysis ToolPak add-in, distributed with Excel, provides another way to calculate a frequency distribution:
If your data is in the form of a table, you may prefer to use a pivot table and a pivot chart to create a histogram. Figure 13.10 shows the student grade data summarized in a pivot table (in D1:E9) and a pivot chart. The counts were created by grouping.
The examples in this section demonstrate how to perform common summing tasks by using formulas. The formulas range from simple to relatively complex array formulas that compute sums by using multiple criteria.
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 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 the following types of arguments, listed here in the order of their presentation:
B1
5
"6"
, ,
SQRT(4)
A1:A5
TRUE
You may want to display a cumulative sum of values in a range — sometimes known as a “running total.” Figure 13.11 illustrates a cumulative sum. Column B shows the monthly amounts, and column C displays the cumulative (year-to-date) totals.
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 into cell C2 and copied down the column, is
=IF(B2<>"",SUM(B$2:B2),"")
Figure 13.12 shows this formula at work.
The SUM
function does not work if the range to be summed includes errors. For example, if one of the cells to be summed displays #N/A
, the SUM
function will also return #N/A
.
To add the values in a range and ignore the error cells, use the AGGREGATE
function. For example, to sum a range named Data (which may have error values), use this formula:
=AGGREGATE(9,6,Data)
The AGGREGATE
function is versatile and can do a lot more than just add values. In this example, the first argument (9
) specifies SUM
. The second argument (6
) means ignore error values.
The arguments are described in the Excel Help. Excel also provides good autocomplete assistance when you enter a formula that uses this function.
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 composed 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 20 values in the Data range. You can, of course, substitute a different value for 20. Figure 13.13 shows this array formula in use.
{=SUM(LARGE(Data,ROW(INDIRECT("1:20"))))}
Often, you need to calculate a conditional sum. With a conditional sum, values in a range that meet one or more conditions are included in the sum. This section presents examples of conditional summing by using a single criterion.
The SUMIF
function is useful for single-criterion sum formulas. The SUMIF
function takes three arguments:
range
: The range containing the values that determine whether to include a particular cell in the sum.criteria
: An expression that determines whether to include a particular cell in the sum.sum_range
: Optional. The range that contains the cells you want to sum. If you omit this argument, the function uses the range specified in the first argument.The examples that follow demonstrate the use of the SUMIF
function. These formulas are based on the worksheet shown in Figure 13.14, set up to track invoices. Column F contains a formula that subtracts the date in column E from the date in column D. A negative number in column F indicates a past-due payment. The worksheet uses named ranges that correspond to the labels in row 1.
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 whether 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 in the argument 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)
The following formula returns the total invoice amounts that have a due date after May 1, 2016:
=SUMIF(DateDue,">="&DATE(2016,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)
All the examples in the preceding section used a single comparison criterion. The examples in this section involve summing cells based on multiple criteria.
Figure 13.15 shows the sample worksheet again, for your reference. The worksheet also shows the result of several formulas that demonstrate summing by using multiple criteria.
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:
Oregon.
If the worksheet won't be used by anyone running a version prior to Excel 2007, the following formula does the job:
=SUMIFS(Amount,Difference,"<0",Office,"Oregon")
The following array formula 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:
Oregon
.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:
Oregon
or California
.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)}