COUNT in Excel (Formula, Examples) | Free Excel Template (2023)

What is COUNT in Excel?

The COUNT in Excel is a function that counts the number of cells that consists of numeric values in a selected range and ignores all the other entries in the range. For example, the formula “=COUNT(A6:A20)” counts all the cells with numerical values (code number) in the cell range A6:A20, which corresponds to 7.

The COUNT function counts numeric values including the date, time, percentages, negative numbers, formulas, and fractions.

Key Highlights

• The COUNT in Excel is a completely programmed function that can be used for an array
• The COUNT function family has a total of five variants- COUNT, COUNTIF, COUNTIFS, COUNTA, and COUNTBLANK
• To count logical values we use the COUNTA variant of the COUNT function family
• To count numbers meeting certain criteria we use either COUNTIF or COUNTIFS function in Excel
• The function COUNT in Excel does not count formula errors and logical values
• The COUNT function counts dates too as Microsoft Excel stores the dates as serial numbers
• The function COUNT in Excel does not count the logical values- TRUE or FALSE

COUNT in Excel Syntax:

The syntax for the COUNT Function in Excel is-

Explanation:

Value1: It is a required argument of the COUNT function that indicates the first item or cell of the specified range.

Value2: It is an optional argument of the COUNT function in Excel that denotes the second set of cells or ranges that we wish to count. Once we put the first Value1, all other values become optional.

Note: We can provide up to 256 values to the COUNT function.

The return of the COUNT function is always either zero or greater than zero.

How to use the COUNT in Excel?

To understand how we can use the function COUNT in Excel, consider the below examples.

Example #1

The table below shows a list of countries with numerical codes. We want to find the total number of numerical codes from the given list using the COUNT function.

Solution:

Step 1: Place the cursor in cell C7 and enter the formula,

=COUNT(A6:A20)

The above formula will count the numeric values in the given list as shown below

Step 2: Press the Enter key to get the below result

The selected range contains 15 values, but the COUNT function in Excel only counts the numerical values and ignores everything else. As a result, it returns 4 as the total number of numerical codes.

Example #2

The table below shows a list of dates. We want to count the total number of dates using the COUNT in Excel function.

Solution:

Step 1: Place the cursor in cell C8 and enter the formula,

=COUNT(A6:A12)

Step 2: Press the Enter key to get the below result,

The total number of selected cells is seven, but the COUNT function returned the value 5 because two dates in the given list are written in an incorrect format.

The below image shows the dates with incorrect format (highlighted in RED)

Example #3

The table below shows the IDs of five employees, as well as their wages and attendance for the first week of January 2023. If an employee is present, his attendance is marked as 1, and if he is absent, his attendance is marked as A. We want to use the COUNT function in Excel to calculate the Employee’s total wages based on his attendance for the week.

Solution:

Step 1: Place the cursor in cell J6 and enter the formula,

=COUNT(C2:I2)

Step 2: Press the Enter key to get the Total no. of Paid Days as shown below

The COUNT in Excel function returns the Total no. of Paid Days as 6.

Now,

Step 3: Place the cursor in cell K2 and enter the formula,

=B6/7*J6

Step 4: Press the Enter key to get the Total Wages of the week for Empl ID 1005

Step 5: Follow the same steps to get the Total wages for all the Emp IDs to get the below result

COUNT in Excel with IF condition

Syntax-

=IF(logical_test,[value_if_true],[value_if_false])

Example #4

Consider the above example of employees with their IDs, wages, and weekly attendance. Using the COUNT and IF functions, we want to find the employees who are eligible for Full Payment.

Solution:

Step 1: Place the cursor in cell L2 and enter the formula,

=IF (COUNT(C6:I6)=7,”Full Pay”,“Not Full Pay”)

Explanation:

COUNT(C6:I6): There are 7 working days in the week, therefore, an employee present on all the days will be the one eligible for Full Payment.

Thus, the condition is written as COUNT(C6:I6)=7

Step 2: Press the Enter key to get the below result

Now,

Step 3: Enter the same formula in the remaining cells to get the below output

The COUNT function combined with the IF condition shows that only the person with Emp ID- 1D006 is eligible for Full Payment. Since all other employees were absent on one or the other day in that week they are not eligible for full payment.

Difference Between COUNT and COUNTA

• The function COUNT in Excel counts the number of cells having numeric values within a cell range whereas the COUNTA function counts the number of non-empty or blank cells within a given range.
• The function COUNT in Excel counts numeric values and dates whereas the function COUNTA counts all the cells within a range irrespective of the data type.

Syntax of COUNTA function is-

=COUNTA (value1, [value2], …)

Difference between COUNT and COUNTA with Example

The table below shows 10 rows with 6 numeric codes, 2 non-numeric codes, and 2 blank cells.

The COUNT function counts the number of cells with numeric codes and gives the result of 6.

The COUNTA function counts all the cells having codes and excludes the empty cells and gives the result of 9.

Things to Remember

• Only numerical values are counted in the COUNT function.
• The COUNT function ignores empty cells, text and string values, and error values in the array.
• If the COUNT function is applied to an empty range of cells, then the result will always be zero.
• If a text follows the number, COUNT ignores that value also. For example, =COUNT (“145 Number”) would return the result as 0.
• If logical values such as TRUE or FALSE are supplied to the formula, these logical values will be counted by the COUNT function.
• If the same TRUE or FALSE is supplied in a range, then the result will be zero.
• If you want the count of all the values in the given range, then use COUNTA which counts whatever comes it’s way.

Frequently Asked Questions (FAQs)

Q1) How do I count cells in Excel?

Answer: In Excel, we can count cells using any of the COUNT function variants: COUNT, COUNTA, COUNTIF, and COUNTBLANK.

• COUNT: To count cells with numeric values
• COUNTA: To count non-empty cells
• COUNTBLANK: To count blank or empty cells
• COUNTIF: To count cells meeting specified criteria

Q2) What is the significance of COUNT function in MS Excel Class 9?

Answer: We can use the COUNT() function to sum or add the number of cells that contain numbers in the specified cell. The count function can perform the complex calculation of adding numbers in a large data set thus saving a lot of time and effort.

Q3) What is the main difference between COUNT and Countif?

The COUNT in Excel function counts the number of cells containing numeric data or entries whereas the COUNTIF function counts the number of cells meeting the given criteria.

For example, the table below shows students’ Maths marks out of 50. Here, we use the COUNTIF function to count the number of students who have scored more than or equal to 35 and passed the test.

Recommended Articles

The above article is EDUCBA’s guide on using the function COUNT in Excel. For more information related to Excel formulas and functions, EDUCBA recommends the below articles.

1. COUNTIFS in Excel
2. COUNTA Function in Excel
3. Count Names in Excel
4. Count Characters in Excel