See 7 ways to count in Excel. Examples and video tutorials show how to count Excel cells with numbers, text, blanks, or cells that contain specific words or other criteria
To see a quick overview of 7 ways to count in Excel, watch this short slide show, or see the steps for using each method, in the video below.
Download the 7 Ways to Count sample workbook, so you can follow along with the video.
This video shows the COUNT function, the COUNTA function, and the COUNTBLANK function, and warns of the quirks in counting some types of cells.
Written instructions are below the video. Count Cells with Numbers, Count Cells with Data and Count Blank Cells. Also, download the sample workbook, to see all the formulas.
The COUNT function will count cells that contain numbers. Its syntax
is:
=COUNT(value1, value2,...value30).
The arguments (e.g. value1) can be cell references, or values typed
into the COUNT formula.
The following COUNT function example uses one argument -- a reference to cells A1:A5.
Note: Since dates are stored as numbers, the COUNT function will include any cells that contain dates.
The COUNTA function will count cells that are not empty. (See note on blank cells below)
Its syntax
is:
=COUNTA(value1, value2,...value30).
The arguments (e.g. value1) can be cell references, or values typed
into the formula. The following example uses one argument -- a reference
to cells A1:A5.
The COUNTBLANK function will count cells that are empty. (See note on blank cells below)
Its syntax
is:
=COUNTBLANK(range).
The following example uses a reference to cells A1:A5.
Both COUNTA and COUNTBLANK will count cells with formulas that look empty, if the formula result is an empty string {""). The empty string is treated as text, and counted.
For example, =IF(B2="","",B2).
Both COUNTA and COUNTBLANK will also count cells which had formulas that returned an empty string, but then were converted to values, by copying and pasting as values.
To fix cells that look blank, but are being counted, see my Contextures blog: Fix Blank Excel Cells Copied From Database ▲TOP
This video shows how to use the COUNTIF function to count cells that contain a specific string of text, such as "Pen". You can also find text that is part of a cell -- how many orders were placed for any kind of pen, such as "Gel Pen", "Pen" or even a "Pencil"?
The written instructions are below the video: Match criterion exactly and Match criterion in a string
In Excel, count cells that meet a specific criterion. In this example only the Pen orders will be counted.
In Excel, count cells that contain a criterion as part of the cell's contents. In this example all Pen, Gel Pen, and Pencil orders will be counted, because they contain the string "pen".
Note: Instead of typing the criterion in a formula, you can
refer to a cell. For example, the formula in step 7 above could be
changed to:
=COUNTIF(A1:A10,"*" & B12 &
"*")
if cell B12 contained the text pen.
There are a few things to be careful with, when using the COUNTIF and COUNTIFS functions.
Be careful when using COUNTIF, because it matches numbers, with text that looks like numbers, and that could result in false counts.
For example, if you have a text entry -- "00123" -- it would be counted as a duplicate for the number -- 123.
=COUNTIF($B$2:$B$10,B2))>1
If your data could contain entries like that, use SUMPRODUCT, instead of COUNTIF.
=SUMPRODUCT(--($B$2:$B$10=B2))>1
COUNTIF and COUNTIFS can only check strings up to 255 characters. Here's a simple example to show the problem. This formula counts how many times an item appears in the Item column:
In row 5, there is a #VALUE! error, because the text in cell C1 is longer than 255 characters.
To avoid the problem with the 255 character limit, use the SUMPRODUCT function, instead of COUNTIF or COUNTIFS. Here is the revised formula:
Here's how that SUMPRODUCT formula works:
On Microsoft's COUNTIF page, it says you can work around the 255 character limit, by joining two long strings with the concatenate operator (&). Here's an example:
That suggestion does NOT work for me !
The COUNTIF function can find specific letters or numbers in a text string. However, COUNTIF cannot find a specific number within a real number.
In the screen shot below, there are 4 items in the list that contain a "1". The COUNTIF formula in cell A9 gives an incorrect result of 3. It does not count the "1" in cell A2, because that cell contains a real number, not a text string.
Although the COUNTIF function can't count specific numbers within real numbers, the FIND or SEARCH function will locate them.
In the screen shot below, the following formulas are used in columns C, D and E, to look for a "1".
The ISNUMBER function returns True or False, and the two minus signs (double unary) convert True to 1 and False to 0 (zero).
The FIND and SEARCH formulas found all 4 items that contain a "1". The COUNTIF formula only found 3 items.
Instead of checking each row individually, use the SUMPRODUCT function with FIND or SEARCH, to get the total count for the list.
In this example, the formulas give a count of cells that contain a 1. The result is 4 in both cases.
In the next example, the formulas give a count of cells that contain "a". FIND only counts the lower-case "a" (1), and SEARCH counts both the upper-case "A", and lower-case "a" (2).
In this video, see how to use the COUNTIF function to count the number of items in a list that are over or under a specific amount. Written instructions are below the video:
You can use an operator with a criterion. In this example only the rows where the quantity is greater than or equal to ten will be counted.
Note: Instead of typing the criterion in a formula, you can
refer to a cell. For example, the formula in step 8 above could be
changed to:
=COUNTIF(B1:B10,">=" & B12)
if cell B12 contained the number 10.
Or, you could use a function as part of the criterion. For example:
=COUNTIF(A1:A10,"<"&TODAY()) ▲TOP
You can combine COUNTIF formulas, to count rows that are within a range of values. In this example, the formula will count rows where the quantity is between 5 and 10 (inclusive).
Note: Instead of typing the criterion in a formula, you can
refer to a cell. For example, the formula in step 8 above could be
changed to:
=COUNTIF(B1:B10,">=" & B12)
-
COUNTIF(B1:B10,">" & C12)
if cell B12 contained the number 5 and cell C12
contained the number 10. ▲TOP
Instead of typing the operator into the COUNTIF formula, as shown above, you can create a list of all possible operators, and select one from a drop down list. Then, refer to that operator in the formula.
This video shows the steps for setting up the formula, and the written instructions are below the video.
To create a drop down list operators:
Change your COUNTIF formula, to replace the typed operator with a reference to the cell with the drop down list.
=COUNTIF(B2:B11,E6&F6)
Then, select one of the operators from the drop down list in cell E6, and the formula result will change.
This video shows how to use the COUNTIFS function to count cells based on multiple criteria.
Written instructions are below the video:
--Count multiple criteria with COUNTIFS
--Count multiple criteria with SUMPRODUCT
--Count all dates in a specific month and year
In Excel 2007 and later versions, you can use the COUNTIFS function to count rows that meet two or more criteria. In this example only the rows where the item is "Pen" and the quantity is greater than or equal to ten will be counted.
Note: Instead of typing the criterion in a formula, you can refer to a cell, as shown in the second formula below. If using operators, enclose them in double quote marks.
Use typed criteria:
=COUNTIFS(A2:A10,"Pen",B2:B10,">=10")
or cell references:
=COUNTIFS(A2:A10,D3,B2:B10,">=" & E3)
In this example, the SUMPRODUCT function is used to count the rows where the item is "Pen" and the quantity is greater than or equal to ten. This solution will work in older versions of Excel, where there COUNTIFS function is not available.
Note: Instead of typing the criterion in a formula, you can refer to a cell, as shown in the second formula below ▲TOP
Use typed criteria:
=SUMPRODUCT(--(A2:A10="Pen"),--(B2:B10>=10))
or cell references:
=SUMPRODUCT(--(A2:A10=D2),--(B2:B10>=E2))
In this example, there is a date in cell A2, and the order list has dates in cell A5:A26. The following SUMPRODUCT function is in cell D2, and it counts all the dates that have the same month and year as the date in cell A2.
=SUMPRODUCT((MONTH(A5:A26)=MONTH(A2))*(YEAR(A5:A26)=YEAR(A2)))
In this example, there is a formula to check a column that should contain unique values only. It will alert you if any of the values have been duplicated.
In cells A6:C12, there is a named table (tblIDs). In the ID column, each number should be unique, but 2 is entered twice, and 3 is entered twice.
The formula shown below will count how many unique values have been duplicated.
Duplicate ID numbers could cause problems, so we'll create a formula to check for them. To count the duplicated values, enter this formula in cell A4. The details are below:
=SUMPRODUCT((tblIDs[ID]<>"") /
COUNTIF(tblIDs[ID],tblIDs[ID]&"") -
(COUNTIF(tblIDs[ID],tblIDs[ID])=1))
NOTE: To simply highlight duplicate values in a column, use Conditional Formatting.
The SUMPRODUCT formula contains 3 formulas:
Next, A is divided by B, and C is subtracted
Finally, those results are summed, to give the count of duplicated values.
In the formula bar, you can select each formula (A, B, and C), and press F9 to calculate that formula. This screen shot shows the results.
Then, calculate the A/B portion of the formula, to see these results. TRUE is equal to 1, so 1/1 equals 1, and 1/2 equals 0.5.
Next, calculate A/B - C, to see these results. TRUE is 1 and FALSE is zero, so 1-1 equals zero, and 0.5 - 0 equals 0.5:
Finally, the SUMPRODUCT function gives the sum of those numbers, with the result of 2.
You can see the same results if you put each part of the formula on the worksheet, and calculate each row separately.
In this screen shot, you can see the A and B results, then A/B. Next, see the C calculations, and A/B-C. At the bottom of the final column, the sum is shown.
In other parts of your workbook, you can refer to cell A4 (DupIds), to create warning messages, or show a zero, instead of the expected results. For example, show a message with a formula like this:
Or, multiply by 1 (TRUE) or zero (FALSE) in other formulas, based on the number in the DupIDs cell.
After you filter the rows in a list, you can use functions to count only the visible rows.
After you filter the rows in a list, you can use the SUBTOTAL function to count the visible rows.
Note: To subtotal rows which have been either manually hidden or filtered, use 103 as the function number, instead of 3:
=SUBTOTAL(103,D2:D10)
▲TOP
After you filter the rows in a list, you can use the AGGREGATE function to count the visible rows. This function was introduced in Excel 2010, and is similar to SUBTOTAL, but it has 19 functions, compared to SUBTOTAL's 11 functions. Another advantage is that it can ignore errors, as well as hidden rows.
The two visible numbers are counted, and the error in cell D9 is ignored.
Laurent Longre created a formula that lets you work with visible rows
after a filter. For information see, Power Formula Technique in this
article at John Walkenbach's web site (via the WayBack Machine site):
https://web.archive.org/web/20100110043824/ https://j-walk.com/ss/excel/eee/eee001.txt
Incorporating that technique, SUMPRODUCT can be used to count visible items in a filtered table. In the following example, column D has been filtered for amounts greater than 100. The following formula will count the number of visible rows that contain "Pen" in column A.
=SUMPRODUCT(SUBTOTAL(3,OFFSET(A1:A10,ROW(A1:A10)
-MIN(ROW(A1:A10)),,1)), --(A1:A10=A12))
Press the Enter key to complete the formula entry. ▲TOP
In the Excel Expert Newsletter (issue 20, July 8, 2001 - no longer available), there is a formula to count unique items in a filtered list. In this example, the list is filtered for the Central region, and unique items in column D are counted.
=SUM(N(IF(ISNA(MATCH("""",unRge,0)),MATCH(Rge,Rge,0),
IF(MATCH(unRge,unRge,0)=MATCH("""",unRge,0),0,
MATCH(unRge,unRge,0)))=ROW(Rge)-MIN(ROW(Rge))+1))
This is an array formula, so press Ctrl+ Shift + Enter to complete the formula. ▲TOP
Count Criteria in Other Column
Count Cells With Specific Text
Last updated: December 3, 2020 4:41 PM
Contextures RSS Feed