Excel COUNT Function

What is COUNT function in Excel?

The COUNT function is one of the Statistical functions of Excel.

It Counts the number of cells in a range that contains numbers.

We can find this function in Statistical category of the insert function Tab.

How to use COUNT function in excel

1. Click on an empty cell (like F5).

2. Click on the fx icon (or press shift+F3).

3. In the insert function tab you will see all functions.

4. Select STATISTICAL category.

5. Select COUNT function.

6. Then select ok.

7. In the function arguments Tab you will see COUNT function.

8. Value1: value1,value2,.. are 1 to 255 arguments that can contain or refer to a
variety of different types of data, but only numbers are counted.

9. Value2: value1,value2,.. are 1 to 255 arguments that can contain or refer to a
variety of different types of data, but only numbers are counted.

10. You will see the results in the formula result section.

Examples of COUNT function in Excel

1. To count the number of cells that contain numbers in a range of cells, use the following formula: =COUNT(A1:A10)
2. To count the number of cells that meet a certain condition, such as containing a specific text string or being greater than a certain value, use the COUNTIF function. For example: =COUNTIF(A1:A10,”apples”) – This counts the number of cells in the range A1:A10 that contain the word “apples”.
3. To count the number of cells that meet multiple conditions, use the COUNTIFS function. For example: =COUNTIFS(A1:A10,”apples”,B1:B10,”>10″) – This counts the number of cells in the range A1:A10 that contain the word “apples” AND have a value greater than 10 in the corresponding cell in the range B1:B10.
4. To count the number of cells in a row, use the following formula: =COUNT(1:1) – This counts the number of cells in row 1.
5. To count the number of cells in a column, use the following formula: =COUNT(A:A) – This counts the number of cells in column A.
6. To count the number of cells in a sheet, use the following formula: =COUNTA(Sheet1!A1:XFD1048576) – This counts the number of cells on Sheet1.
7. To count the number of cells in a named range, use the following formula: =COUNT(MyRange) – This counts the number of cells in the range named MyRange.
8. To count the number of cells that are not blank in a range, use the following formula: =COUNTA(A1:A10) – This counts the number of non-blank cells in the range A1:A10.
9. To count the number of unique values in a range, use the following formula: =SUM(1/COUNTIF(A1:A10,A1:A10)) – This counts the number of unique values in the range A1:A10.
10. To count the number of cells that contain errors, use the following formula: =COUNTIF(A1:A10,”#N/A”) – This counts the number of cells in the range A1:A10 that contain the #N/A error value.

How the COUNT function in Excel works

The COUNT function in Excel counts the number of cells within a specified range that contain numeric values. It does not count blank cells, text or Boolean values, error values, or cells that contain formulas.

For example, if you have a range of cells A1 through A5 where cells A1 and A2 contain numeric values 10 and 20 respectively, cell A3 contains text “Apple”, while cells A4 and A5 are blank, then the formula =COUNT(A1:A5) will return a value of 2 (counting the numeric values only).

Syntax of the COUNT function in Excel

The syntax of the COUNT function in Excel is as follows:

Copy Code=COUNT(value1, [value2], ...)

• value1 is the first range or argument you want to count.
• [value2] (optional) is the second range or argument you want to count.
• ... (optional) represents additional ranges or arguments that you want to count.

You can specify up to 255 individual ranges or arguments within a single COUNT function in Excel.

Counting cells that meet certain criteria

To count cells that meet certain criteria using the COUNT function in Excel, you can use the COUNTIF function instead.

The COUNTIF function counts the number of cells within a specified range that match a given criterion.

For example, if you have a range of cells A1 through A5 where cells A1 and A2 contain numeric values 10 and 20 respectively, cell A3 contains text “Apple”, while cells A4 and A5 are blank, and you want to count the number of cells in the range that contain values greater than or equal to 15, you can use the formula =COUNTIF(A1:A5,">=15"). This formula will return a value of 1.

Counting multiple ranges at once

No, the COUNT function in Excel cannot count multiple ranges at once. However, you can use the SUM and the COUNTIFS functions together to count numbers across multiple ranges that meet certain criteria.

For example, if you have two ranges of cells A1 through A5 and B1 through B5, and you want to count the number of cells that contain numeric values greater than or equal to 10 in both ranges, you can use the formula =SUM(COUNTIFS(A1:A5,">=10",B1:B5,">=10")).

This formula will count all cells in both ranges that contain values greater than or equal to 10 and return the total count.

Excluding blank cells from being counted

By default, the COUNT function in Excel counts all cells within a specified range, including those that are blank. To exclude blank cells from being counted, you can use the COUNTA function instead.

For example, if you have a range of cells A1 through A5 where cells A1 and A2 contain numeric values 10 and 20 respectively, cell A3 contains text “Apple”, while cells A4 and A5 are blank, then the formula =COUNTA(A1:A5) will return a value of 3 (counting the non-blank cells only).

Using the COUNT function in Excel with non-numeric values

The COUNT function in Excel only counts cells that contain numeric values. If you want to count all cells within a specified range, including non-numeric values, you can use the COUNTA function instead.

For example, if you have a range of cells A1 through A5 where cells A1 and A2 contain numeric values 10 and 20 respectively, cell A3 contains the text “Apple”, while cells A4 and A5 are blank, then the formula =COUNTA(A1:A5) will return a value of 3 (counting all non-blank cells).

Counting cells based on their font color or background color

No, the COUNT function in Excel cannot count cells based on their font color or background color. However, you can use other functions like COUNTIF or SUMPRODUCT in combination with conditional formatting to count cells based on their color.

For example, you can set up conditional formatting rules that highlight cells based on certain criteria, and then use the COUNTIF function to count the number of cells that meet those criteria.

Counting unique values using the COUNT function in Excel

To count unique values using the COUNT function in Excel, you can use an array formula that combines the COUNTIF and SUM functions.

For example, if you have a list of values in cells A1 through A10, and you want to count the number of unique values in the list, you can use the following formula:

Copy Code=SUM(1/COUNTIF(A1:A10,A1:A10))

This formula creates an array of reciprocal values for each unique value in the range A1:A10, and then sums those values to produce the final count of unique values.

Note that this is an array formula, so you need to press Ctrl + Shift + Enter instead of just Enter to enter it into the cell.

Does the COUNT function in Excel count cells with errors?

Yes, the COUNT function in Excel counts cells with errors. It includes all cells within the specified range, regardless of whether they contain numeric values or errors.

For example, if you have a range of cells A1 through A5 where cells A1 and A2 contain numeric values 10 and 20 respectively, cell A3 contains the #VALUE! error, while cells A4 and A5 are blank, then the formula =COUNT(A1:A5) will return a value of 5 (counting all cells).

Counting cells within a certain range

To count the number of cells within a certain range using the COUNT function in Excel, simply specify the range of cells you want to count within the function’s parentheses.

For example, if you have a range of cells A1 through A5 and you want to count the number of cells within that range, you can use the formula =COUNT(A1:A5). This formula will return the total count of cells within the specified range.

Counting the number of occurrences of a specific value or text string

To count the number of occurrences of a specific value or text string using the COUNT function in Excel, you can use the COUNTIF function instead.

For example, if you have a list of values in cells A1 through A10, and you want to count how many times the value “apple” appears in the list, you can use the formula =COUNTIF(A1:A10,"apple").

This formula will count the number of cells in the range A1 through A10 that contain the exact string “apple”.

Can the COUNT function in Excel count cells that are not adjacent to each other?

Yes, the COUNT function in Excel can count cells that are not adjacent to each other. You can do this by specifying multiple non-contiguous ranges within the function’s parentheses, separated by commas.

For example, if you have data in cells A1 through A5 and C1 through C5, and you want to count the total number of cells in both ranges, you can use the formula =COUNT(A1:A5, C1:C5). This formula will count all cells within both ranges and return the total count.

Using the COUNT function in Excel with logical operators such as AND and OR

To use logical operators such as AND and OR with the COUNT function in Excel, you can use the COUNTIFS function instead.

The COUNTIFS function allows you to specify multiple criteria using logical operators.

For example, if you have a range of cells A1 through A5 where cells A1 and A2 contain numeric values 10 and 20 respectively, cell A3 contains the text “Apple”, while cells A4 and A5 contain numeric values 30 and 40 respectively, and you want to count the number of cells that contain values greater than or equal to 15 and less than or equal to 35, you can use the formula =COUNTIFS(A1:A5,">=15",A1:A5,"<=35").

This formula will return a value of 2.

Counting cells in a filtered range

If you want to count cells in a filtered range in Excel, you can use the SUBTOTAL function instead of the COUNT function.

This is because the COUNT function counts all cells within a specified range, regardless of whether they are hidden by a filter or not.

For example, if you have a dataset in A1 through B10, and you apply a filter to column A, you can use the formula =SUBTOTAL(3,A2:A10) to count the number of non-blank cells in column A, ignoring the rows that are hidden by the filter.

Limit to how many cells the COUNT function in Excel can count at once

The COUNT function in Excel can count up to 2^20 (1,048,576) cells at once. However, it is important to note that counting a large number of cells may slow down Excel’s processing speed.

Nesting the COUNT function in Excel within another function

You can nest the COUNT function within another function in Excel to perform more complex calculations. To do this, you simply need to replace the range argument in the nested function with the COUNT function.

For example, if you have a range of cells A1 through A10 where you want to count the number of cells that contain values greater than 5 and less than 15, you can use the formula =SUM(IF((A1:A10>5)*(A1:A10<15),COUNT(A1:A10))).

This is an array formula, so you need to press Ctrl + Shift + Enter instead of just Enter to enter it into the cell.

count cells in a named range

To use the COUNT function in Excel to count cells in a named range, simply specify the name of the range instead of the range’s cell references.

For example, if you have a named range “Data” that includes cells A1 through A10, and you want to count the number of non-blank cells within that range, you can use the formula =COUNT(Data).

Whether the COUNT function in Excel counts cells that contain formulas

The COUNT function in Excel counts all cells within a specified range, including those that contain formulas. However, it only counts cells that contain numeric values, not formulas or text.

For example, if you have a range of cells A1 through A5 where cells A1 and A2 contain numeric values 10 and 20 respectively, cell A3 contains the formula “=A1+A2”, while cells A4 and A5 are blank, then the formula =COUNT(A1:A5) will return a value of 2 (counting only the numeric values in cells A1 and A2).

Counting cells in a row or column using the COUNT function in Excel

To count cells in a row or column using the COUNT function in Excel, simply specify the entire row or column as the range within the function’s parentheses.

For example, if you have a dataset in A1 through D10 and you want to count the number of non-blank cells in column B, you can use the formula =COUNT(B:B). Similarly, if you want to count the number of non-blank cells in row 2, you can use the formula =COUNT(2:2).

count cells across multiple worksheets or workbooks

To use the COUNT function in Excel to count cells across multiple worksheets or workbooks, you can use the SUM or SUMPRODUCT function together with the COUNTIF function.

For example, if you have data in cell A1 of sheets “Sheet1” and “Sheet2”, and you want to count the number of non-blank cells in those two cells across both sheets, you can use the formula =SUMPRODUCT(COUNTIF(INDIRECT({"Sheet1!A1","Sheet2!A1"}),"<>")).

This formula creates an array of cell references for each sheet using the INDIRECT function, counts the number of non-blank cells within each reference using the COUNTIF function, and then sums those counts using the SUMPRODUCT function to produce the final count.

COUNT related functions 

• Use COUNTA function to count the number of cells in a range that are not empty.
• Use COUNTBLANK function to count the number of empty cells in a specified range of cells.
• Use COUNTIF function to count the number of cells within a range that meet the given condition.
• Use COUNTIFS function to count the number of cells specified by a given set of conditions or criteria.

Related posts:

Excel COUNTBLANK Function Excel CORREL Function Excel LEFT Function Excel COUNTA Function