Home > Formulas > Count > Examples

Excel Count Functions

Excel Count formula examples. Count filtered rows. COUNTIF one or more criteria. Count numbers, text, blank cells. Short videos, Excel files, written steps

Overview: 7 Ways to Count in Excel

To see a quick overview of 7 ways to get a total count of cells in Excel, watch this short video. There are written steps for each count function below the video.

Count Numbers, All Data, or Blank Cells

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, get the sample workbook, to see all the formulas.

COUNT Function: Count Numbers

The COUNT function will count cells that contain numbers.

Its syntax has one required argument, and up to 255 additional items, that are optional:

• =COUNT(value1, [value2],...])

COUNT Function Notes

• COUNT function arguments can be cell references, or values typed into the COUNT formula.
• Because dates are stored as numbers in Excel, the COUNT function will count any cells that contain valid dates.
• You can refer to cell ranges that include non-numeric values, but only the cells with numbers will be counted

COUNT Function Example

In the screen shot below, there's a variety of data entered in column A:

• A2: number
• A3: text
• A4: date
• A5: blank cell

The following COUNT formula is entered in cell A7, using one value argument - a reference to a range of cells:

 =COUNT(A2:A5)  

Excel counts the number (A2) and the date (A4), so the formula result, in cell A7, is a count of 2.

Note: The text value and the blank cell are not counted.

COUNT Function Results

To verify the count, I used the COUNT function in cells B2:B7, to count each item in column A.

• Here is the formula in cell B2: =COUNT(A2)
• The formula was copied down to cell B5
• In each row, the formula result is 1 or zero, and the total for column B is 2 -- the same result as the COUNT formula in cell A7

Verify with ISNUMBER

For a different verification method, I use the ISNUMBER function in column C, to check each value in column A.

• Here is the formula in cell C2: =ISNUMBER(A2)
• The formula was copied down to cell C5
• In each row, the formula result is TRUE or FALSE
• The number of TRUE results is 2 -- the same result as the COUNT formula in cell A7

COUNTA: Count Cells with Any Data

The COUNTA function will count cells that are not empty.

It counts cells with any type of information in them, such as formulas, text, numbers, error values, logical values, and even counts the empty strings (""), that some formulas return.

The COUNTA function argument 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.

1. Enter the sample data on your worksheet
2. In cell A7, enter a COUNTA formula, to count the data entries in a range of cells in column A:   =COUNTA(A1:A5)  



3. Press the Enter key, to complete the formula.
4. The result will be 4, the number of cells that contain any type of data, in the specified cell range.

COUNTBLANK: Count Blank Cells

The COUNTBLANK function will count cells that are empty (blank), and cells that contain an empty string

• NOTE: See next section for notes on counting cells that look empty, but contain an invisible "empty string"

Use COUNTBLANK Function

To count cells that are completely empty, use the Excel COUNTBLANK function, which has the following syntax, with one argument -- range:

•     =COUNTBLANK(range)

Follow these steps, to count the blank cells in the worksheet range A1:A5:

1. Enter the sample data, shown below, on your worksheet, in cells A1 to A5
2. Next, in cell A7, enter the following COUNTBLANK formula, to count the blank cells:
•   =COUNTBLANK(A1:A5)  
3. Press the Enter key, to complete the formula.

In this example, the formula result is 1, becasue there is one completely empty cell in the range.

Cells Look Blank - Empty Strings

The COUNTA and COUNTBLANK functions will both count cells that contain empty strings (also called empty text).

For example, in the screen shot below, the following formula returns an empty string (""), if cell A3 is not an odd number:

• =IF(ISODD(A3),1,"")

Cells B3 and B5 look empty, because the formula result in those cells is an empty string.

Counting Empty Strings

If you're counting the same range of cells with different functions, be careful that you aren't counting "empty string" cells twice.

In the example shown above, the empty string cells are counted by the formulas in cells B8 and B9.

• B8: COUNTBLANK function treats an empty string as a blank cell, and includes it in the count
  • Formula result is 2 -- two of the five cells are counted as blank
• B9: COUNTA function treats an empty string as text, and includes it in the count
  • Formula result is 5 -- all five of the five cells are counted as containing values

Empty String Cells Without Formulas

If you import data to Excel, such as query results from Microsoft Access, some cells might look empty, but Excel treats them as containing empty strings.

Also, some data created within Excel can contain empty strings, with no formulas. Those cells are usually created by copying a range where some formulas returned an empty string, then pasting those cells as values.

• NOTE: You can see apostrophes in those "non-blank" converted cells, if you add a check mark to Excel Options, Advanced, Lotus Compatibility, "Transition navigation keys." 

To fix cells that look blank, but are being counted, see my Contextures blog: Fix Blank Excel Cells Copied From Database

COUNTIF: Count with one criterion

This video shows how to use the Excel 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 steps for the Excel COUNTIF function examples are below the video:

1) Match criterion exactly and 2) Match criterion in a string

1) COUNTIF: Exact Match - One criterion

In an Excel spreadsheet, count cells that are an exact match for one specific criterion.

In this example only the Pen orders, with an exact match for the criterion, will be counted.

1. Select the cell in which you want to see the count (cell A12 in this example)
2. Type an equal sign (=) to start the formula
3. Type:   COUNTIF(
4. Select the cells that contain the values to check for the criterion. In this example, cells A1:A10 will be checked
5. Type a comma, to separate the arguments
6. Type the criterion. In this example, you're checking for text, so type the word in double quotes:   "Pen"
   Note: upper and lower case are treated equally
   
7. Type a closing bracket
   The completed formula is: =COUNTIF(A1:A10,"Pen")
   



8. Press the Enter key to complete the entry
9. The result will be 4, the number of cells that contain "Pen"  ▲TOP

2) COUNTIF: Partial Match - One Criterion in a string

In Excel, count cells that contain a criterion as part of the cell's contents -- a partial match.

In this example all Pen, Gel Pen, and Pencil orders will be counted, because they contain the string "pen".

1. Select the cell in which you want to see the count (cell A12 in this example)
2. Type an equal sign (=) to start the formula
3. Type:   COUNTIF(
4. Select the cells that contain the values to check for the criterion. In this example, cells A1:A10 will be checked
5. Type a comma, to separate the arguments
6. Type the criterion. In this example, you're checking for text, so type the word in double quotes, with one or more asterisk (*) wildcard characters:   "*Pen*"
   Note: upper and lower case are treated equally
   
7. Type a closing bracket
   The completed formula is: =COUNTIF(A1:A10,"*Pen*")
   



8. Press the Enter key to complete the entry
9. The result will be 6, the number of cells that contain the string, "Pen"  ▲TOP

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.

Count cells that match multiple criteria

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

Count Cells that Match multiple criteria

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.

1. Select the cell in which you want to see the total
2. Type an equal sign (=) to start the formula
3. Type:   COUNTIFS(
4. Select the cells that contain the values to check for the first criterion. In this example, cells A2:A10 will be checked
5. Type a comma, and the first criterion:   "Pen"
   Note: Because this is a text criterion, it is enclosed in double quote marks.
   
6. To start the next set of criteria, type a comma
7. Select the cells that contain the values to check for the second criterion. In this example, cells B2:B10 will be checked
8. Type a comma, and the second criterion:  ">=10"
   Note: Because this criterion includes operators, it is enclosed in double quote marks. To count rows where the quantity is equal to 10, only the number 10 would be required.
   
9. Finish with a closing bracket: )
10. The completed formula is shown below.
    
11. Press the Enter key to complete the entry

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)

Count Numbers with Criteria

In this video, see how to use 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:

• Greater Than or Equal To
• Between 5 and 10 (In a range)
• Choose the COUNTIF Operator from a List

Count Cells Greater Than or Equal to

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.

1. Select the cell in which you want to see the count (cell A12 in this example)
2. Type an equal sign (=) to start the formula
3. Type:   COUNTIF(
4. Select the cells that contain the values to check for the criterion. In this example, cells B1:B10 will be checked
5. Type a comma, to separate the arguments
6. Type the criterion. In this example, you're checking for rows where the quantity is greater than or equal to 10. The >= operator is used before the number, and the entire criterion is enclosed in double quotes: ">=10"
   Note: Even though this is a numerical criterion, it must enclosed in double quote marks.
   
7. Type a closing bracket
8. The completed formula is:
             =COUNTIF(B1:B10,">=10")
   
9. Press the Enter key to complete the entry

   

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

Count Numbers Between Min and Max

To count numbers in a specific range, such as "between 5 and 10", you can use either:

1. COUNTIFS function
2. COUNTIF function

There is an example for each function in the sections below, and notes on using cell references for the minimum and maximum numbers, instead of typing them in the formulas.

Note: I recommend using COUNTIFS, for Excel 2010 and later, because the formula is shorter, and easier to understand.

1) Count Numbers in Range - COUNTIFS

In this example, the COUNTIFS formula will count rows where the number in column B is between 5 and 10 (inclusive).

Enter the following formula in the cell where you want the count -- I used cell B12 in the screen shot below. The criteria and operators are enclosed in double quote marks.

• =COUNTIFS(B1:B10,">=5",B1:B10,"<=10")

The formula result is 6, because there are six product sales with quantity that meets the two criteria:

• greater than or equal to 5
• AND
• less than or equal to 10

2) Count Numbers in Range - COUNTIF

To get the count with the COUNTIF function, you'll need a formula that uses 2 instances of COUNTIF.

Just like the COUNTIFS example shown above, this COUNTIF formula will count rows where the quantity is between 5 and 10 (inclusive).

Enter the following formula in the cell where you want the count -- I used cell B12 in the screen shot below. The criteria and operators are enclosed in double quote marks.

• =COUNTIF(B1:B10,">=5")-COUNTIF(B1:B10,">10")

The formula result is 6, because there are:

• 9 products with quantity greater than or equal to 5
• 3 products with quantity greater than 10
• Subtract the 3 numbers that are too high: 9-3 = 6

Cell References for Criteria

Instead of typing the criterion in a formula, you can refer to a worksheet cell, where the criteria values are stored. This makes it easier to adjust the number range - you don't have to modify the formula, just the worksheet cells.

For example, the COUNTIFS formula above could be changed to:

• =COUNTIFS(B1:B10,">=" & B12, B1:B10,"<=" & B13)

In this example:

• cell B12 contains the minimum number
• cell B13 contained the maximum number.

Count - Multiple Criteria - Other Functions

In addition to COUNTIFS, you can use other Excel functions for advanced counting formulas

-- 1) Count Cells - Multiple Criteria - SUMPRODUCT

-- 2) Count Dates - Specific Month and Year

1) Count Cells - Multiple Criteria - SUMPRODUCT

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.

1. Select the cell in which you want to see the total
2. Type an equal sign (=) to start the formula
3. Type:   SUMPRODUCT(--(
4. Select the cells that contain the values to check for the first criterion. In this example, cells A2:A10 will be checked
5. Type the first criterion:   ="Pen"
   Note: Because this is a text criterion, it is enclosed in double quote marks.
   
6. Type ),--(
7. Select the cells that contain the values to check for the second criterion. In this example, cells B2:B10 will be checked
8. Type the second criterion:   >=10
   Note: Because this is a numerical criterion, it isn't enclosed in double quote marks.
   
9. Finish with closing brackets: ))
10. The completed formula is shown below.
    
11. Press the Enter key to complete the entry

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

2) Count All Dates in Specific Month and Year

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

• The MONTH function returns the month number for each cell, and compares that to the month number for the date in cell A2.
• The YEAR function returns the year number for each cell, and compares that to the year number for the date in cell A2.
• Only the rows where both the month and year match cell A2 are counted.

Change Operator for COUNTIF Function

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.

Create a Drop Down List of Operators

To create a drop down list operators:

1. On a different sheet in the workbook, type a list of operators in a column.
2. Select the cells in the list, and name that list as OpList.

   

3. On the main sheet, select the cell where you want the drop down list -- cell E6 in this example
4. Use the data validation command to create the drop down list, based on the named range -- OpList

Use the Drop Down List

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.

Count Numbers in Numbers

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.

Use FIND or SEARCH

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

• C2: =COUNTIF(A2,"*1*")
• D2: =--ISNUMBER(FIND(1,A2))
• E2: =--ISNUMBER(SEARCH(1,A2))

The FIND and SEARCH formulas found all 4 items that contain a "1". The COUNTIF formula only found 3 items.

Use SUMPRODUCT with FIND or SEARCH

Instead of checking each row individually, use the SUMPRODUCT function with FIND or SEARCH, to get the total count for the list.

• Use FIND to count numbers, or case-sensitive letters
• Use SEARCH to count numbers, or upper and lower case letters (not case-sensitive)

Example 1: Numbers

In this example, the formulas give a count of cells that contain a 1. The result is 4 in both cases.

• A9:    =SUMPRODUCT(--ISNUMBER(FIND(1,$A$2:$A$7)))
• A10:  =SUMPRODUCT(--ISNUMBER(SEARCH(1,$A$2:$A$7)))

Example 2: Letters

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

• A9:   =SUMPRODUCT(--ISNUMBER(FIND("a",$A$2:$A$7)))
• A10: =SUMPRODUCT(--ISNUMBER(SEARCH("a",$A$2:$A$7)))

Count Rows in Filtered List - SUBTOTAL

After you filter the rows in a list, you can use the SUBTOTAL function to count the visible rows in the filtered data.

• NOTE: For more examples for how to count rows in a filtered list, go to the Excel Filtered Rows Count or Sum page.

SUBTOTAL Syntax

• SUBTOTAL(function_num,ref1,[ref2],...)

In the SUBTOTAL function syntax, there are two required arguments:

• function_num: (required) number that specifies which function to use for the subtotal.
• For details on the function numbers, go to the Excel SUBTOTAL function page.
• ref1: (required) first range of cells that you want to subtotal

SUBTOTAL Example

In this example, there is a list on the worksheet, from cell A1 to D10.

In column A, a filter has been applied, to show Binder Items only.

In cell A12, the following formula is entered:

• =SUBTOTAL(3,D2:D10)

SUBTOTAL Formula

In the above formula:

• first argument (function_num) is 3 (COUNTA)
  • both text and numbers will be counted
• second argument (ref1) is the range D2:D10
  • values in the visible rows of that rante will be counted

Filtered or Manually Hidden

In the first set of function numbers for the SUBTOTAL function, only the rows hidden by filtering are ignored.

If you also want to ignore rows that were manually hidden, use the "100-range" numbers instead.

In the screen shot below:

• Function 1: AVERAGE - will NOT ignore any manually hidden rows
• Function 101: AVERAGE - WILL ignore any manually hidden rows

• NOTE: For more examples for how to count rows in a filtered list, go to the Excel Filtered Rows Count or Sum page.

Count Rows in Filtered List With AGGREGATE

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.

1. Apply an AutoFilter to the table. There are instructions here -- AutoFilter Basics
2. Filter at least one of the columns in the table. In this example, the first column has been filtered for Binders.
3. Select the cell in which you want to see the total -- cell B1 in this example
4. To start the formula, type:   =AGGREGATE(
5. In the list of function numbers, double-click on 3-COUNTA, then type a comma
6. In the list of option numbers, double-click on 3 - Ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE functions, then type a comma
7. Select the cells that contain the values to check for the first criterion. In this example, the Total column in the table is selected.
8. Type a closing bracket, then press the Enter key to complete the formula entry.
   =AGGREGATE(3,3,Table1[Total])

The two visible numbers are counted, and the error in cell D9 is ignored.

Count Specific Items in a Filtered List

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.

1. From the drop down list in cell D1, select Custom.
2. Filter for rows greater than 100.
3. In cell A12, type: Pen
4. In cell B12, enter the following formula:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A1:A10,ROW(A1:A10)
              -MIN(ROW(A1:A10)),,1)), --(A1:A10=A12))

5. Press the Enter key to complete the formula entry.   ▲TOP

Count Unique Items in a Filtered List

Here are two ways that you can count the number of unique items in a filtered list:

1. Helper Columns: Add extra "helper" columns in your list, to check each row for a unique item. Then, use that column to get the total count of unique items. This method is easier to understand, but on some worksheets you might not want to add an extra item
2. Array Formula: If you can't add helper columns to your list, use a complex array-entered formula instead. This can be entered in a single cell on the worksheet, so it requires less space. However, the formula is more difficult to understand.

1) Helper Columns for Unique Visible Item Count

Thanks to Alex J who shared his technique for counting the unique visible items in a filtered list.

In the screenshot below, the formula in column D shows a 1 for the first instance of each item, and a 0 for all subsequent instances. For example, in cell D7, there’s a 1, counting item 91-AB145, and in the next two rows the count for the same item is zero.

The formula in cell D5 is:

=(COUNTIF($C$5:$C5,$C5)<2)*1

Apply a Filter

However, when the list is filtered to show only the Central region items, the total in cell D2 still shows 16 unique items, and cell D11 shows a zero for item 91-AB146, even though it’s the first visible instance of that item.

To solve the problem, AlexJ added some helper columns to the table. First, in column E, he created a formula to check if the row is visible. The formula in cell E5 is:

=1*SUBTOTAL(3,$D5)

AlexJ uses the 1 multiplier at the start of the formula to avoid the problem of the last row not being included in the AutoFilter range.

Based on this new formula, the total in cell E2 correctly shows that only 6 rows are visible.

Test For Visible Rows

Next, in column F, AlexJ created a formula to show the item name if the row is visible. For hidden rows, the formula displays a hyphen instead of the item name. In cell F5, the formula is:

=IF($E5,$C5,”-“)

In cell G1, I tested the result for hidden cell F6, and you can see the result is a hyphen.

Count Unique Visible Items

Finally, in column G, AlexJ created a formula to check for unique items in column F, where only the visible rows have an item name. In cell G5, the formula is:

=($F5<>”-“)*(COUNTIF($F$5:$F5,$F5)<2)

With this formula, the hidden rows are ignored, and the count of unique items in cell G2 is correct for the filtered rows. The item 91-AB145 is counted only once, even though it’s in the filtered results twice, and item 91-AB146 is counted, even though it’s not the first instance of that item in the full list.

2) Array Formula for Unique Visible Item Count

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 to show only the Central region, and unique visible items in column D are counted.

1. The LineVal column (cells C5:C27) is a named range -- Rge
2. The name unRge is defined with the following formula:
• =IF(SUBTOTAL(3,OFFSET(Rge, ROW(Rge)- MIN(ROW(Rge)),,1)), Rge,"")
3. In cell C2, enter the following formula, then press Ctrl+ Shift + Enter:

• =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))

Count Duplicated Items in List

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.

• In this example, the formula result should be 2 -- there are 2 unique values that have been duplicated - 2 and 3.
• The formula will NOT tell us that 4 rows contain duplicated ID numbers.

Formula to Count Duplicated Items

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.

How It Works

The SUMPRODUCT formula contains 3 formulas:

• A: (tblIDs[ID]<>"") - Check if the cell is NOT empty - TRUE (1) or FALSE (0)
• B: COUNTIF(tblIDs[ID],tblIDs[ID]&"") - How many times does the ID appear in the column
• C: (COUNTIF(tblIDs[ID],tblIDs[ID])=1) - Number (B) is equal to one - TRUE (1) or FALSE (0)

Next, A is divided by B, and C is subtracted

Finally, those results are summed, to give the count of duplicated values.

Calculate in the Formula Bar

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.

Worksheet Formulas

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.

Use the Formula Result

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:

• =IF(DupIDs>0,"Before continuing, remove duplicate IDs.","")

Or, multiply by 1 (TRUE) or zero (FALSE) in other formulas, based on the number in the DupIDs cell.

• =COUNT(tblIDs[ID])*--(DupIDs=0)

COUNTIF and COUNTIFS Warnings

There are a few things to be careful with, when using the COUNTIF and COUNTIFS functions.

1. Count might include false duplicates
2. Cannot count text strings longer than 255 characters
3. Similar text might be counted, instead of just specific text - see the example here
4. Specific numbers within other numbers are not counted - use other functions

1) False Duplicates With COUNTIF

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

2) Character Limit of 255

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:

• =COUNTIF([Item],[@Item])

In row 5, there is a #VALUE! error, because the text in cell C1 is longer than 255 characters.

Use a Different Function

To avoid the problem with the 255 character limit, use the SUMPRODUCT function, instead of COUNTIF or COUNTIFS. Here is the revised formula:

• =SUMPRODUCT(--([Item]=[@Item]))

Here's how that SUMPRODUCT formula works:

• Checks each row [Item] to see if it's equal to the entry in the current row [@Item]
• The result is TRUE or FALSE for each row (highlighted in the screen shot below -- I pressed F9 to evaluate that section of the formula)
• The two minus signs (double unary) change TRUE to 1 and FALSE to 0
• SUMPRODUCT adds up the 1s and 0s.

Microsoft Workaround Does Not Work

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:

• =COUNTIF(A2:A5,"long string"&"another long string")

That suggestion does NOT work for me !

Get the COUNT Sample Files

1. Download the Count Functions sample workbook. The zipped file is in xlsx format, and does not contain any macros.
2. Download the 7 Ways to Count sample workbook, to follow along with the 7 Ways to Count video. The zipped file is in xlsx format, and does not contain any macros.
3. Download the Character Limit 255 workbook. The zipped file is in xlsx format, and does not contain any macros.
4. Download the Count Unique Items in Filtered List workbook. The zipped file is in xlsx format, and does not contain any macros.
5. Download the Count Duplicate Number Sets workbook. Find duplicate sets of 6 numbers, which can be in any order in the row. The zipped file is in xlsx format, and does not contain any macros. NOTE: You will see an alert about a data connection, because the file contains a Power Query solution.

More Function Tutorials

Filtered Rows, Count

Date Range, Sum or Count

Calculation Options

Count Criteria in Other Column

Count Specific Items

Count Specific Items in Cell

Count Cells With Specific Text

AVERAGE

SUM / SUMIF

Subtotal Feature