Updated July 19, 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: A required argument of the COUNT function 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 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?
Consider the examples below to understand how we can use the function COUNT in Excel.
Example #1
The table below shows a list of countries with numerical codes. Using the COUNT function, we want to find the total number of numerical codes from the given list.
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 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; if 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 weekly attendance.
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-
Example #4
Consider the above example of employees with IDs, wages, and weekly attendance. Using the COUNT and IF functions, we want to find eligible employees 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 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
Combined with the IF condition, the COUNT function 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 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-
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, 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, 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, the COUNT function will count these logical values.
- The result will be zero if the same TRUE or FALSE is supplied in a range.
- If you want the count of all the values in the given range, 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 the 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 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.
