COUNT Function in Excel (Table of Contents)
COUNT in Excel
Count function in excel is used to count the numbers only from any selected range which can be a row, column or any matrix. For counting any range for number, we just need to select the complete range, then count function will return us the numbers that are in the selected range. It does not consider any other input other than the number.
COUNT Formula in Excel:
The Formula for the COUNT Function in Excel is as follows:
The Formula of COUNT Function many of value1, value2, value 3.
Value 1: This is the mandatory parameter. This is the first cell or range we are counting.
Value 2: This is the second set of cell or range we are looking to count. However, it is not a mandatory argument. Once the first Value 1 is, all other values become optional.
Note: we can give up to 256 values to COUNT function.
The return of the COUNT function is always either zero or greater than zero.
How to Use COUNT Function in Excel?
This COUNT Function is very simple easy to use. Let us now see how to use the COUNT Function in Excel with the help of some examples.
Consider the below data and apply COUNT function to find the total numerical values in the range.
Apply COUNT function.
It returns the total numeric values as 4.
Look how many cells did COUNT function counted.
In the supplied range there are 15 values are there but COUNT function ignored everything and counted only numerical values (red boxes).
COUNT function can count dates also if it is in the correct format. We will see the below example how COUNT works for date values.
Now apply COUNT function.
COUNT range is from A2:A8 and there are totally 7 date values are there. COUNT function returned a result as only 5.
Look at the red color marked cells, those cells date values are not incorrect date format. Even though excel stores dates as serial numbers COUNT function always ignores wrong formatted date values from the calculation.
COUNT function can be used with other functions also. In this example, I will show you how to use COUNT with IF condition. Below is the attendance data for 7 days. If the employee is present then it is marked as 1 and if the employee is absent then it is marked as A. Total wages for the week is also provided.
There are a couple of ways to calculate the wages. I will explain one by one.
Calculate the paid days and multiply the paid days by Wages.
It returns the result as :
Using COUNT function, I got the total paid days.
To Find Total Wages below Formula is used:
It returns the result as:
To check whether an employee is eligible for full pay or not we can use COUNT with IF condition.
=IF (COUNT (C2:I2) =7,”Full Pay”, “Not Full Pay”)
There are 7 working days in the above data. If condition along with the COUNT function checking whether the total count of number is equal to 7 or not.
If the total count of number is 7 then if condition returns Full Pay if not IF condition returns Not Full Pay.
Things to Remember
- Only numerical values are counted in COUNT function.
- 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 the number is followed by a text, COUNT ignores that value also. For example, =COUNT (“145 Number”) would return the result as 0.
- If the logical values such as TRUE or FALSE supplied to the formula then these logical values will be counted by COUNT function.
- If the same TRUE or FALSE 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 that counts whatever comes in its way.
This has been a guide to COUNT in Excel. Here we discuss the COUNT Formula in Excel and how to use COUNT Function in Excel along with excel example and downloadable excel templates. You may also look at these useful functions in excel –