COUNTIF function , as the name suggests, does the counting job in GOOGLE SHEETS , just like the COUNT FUNCTION with the difference that we can provide condition in COUNTIF FUNCTION which cells to count.
The other difference is that it works independently of the type of data which means that it would work for numbers as well as text or any other data type.
COUNTIF FUNCTION is very helpful when we need to use any nested functions to make a check on the number of cells containing the number or number type of data.
Let us learn ,how to use COUNTIF FUNCTION IN GOOGLE SHEETS .
COUNTIF FUNCTION returns the total number of cells from a given data set or range which fulfill a particular given criteria or condition.
THERE ARE A FEW PREREQUISITES WHICH WILL ENABLE YOU TO UNDERSTAND THIS FUNCTION IN A BETTER WAY.
The Syntax for the function is
RANGE Given range which needs to be scanned to find out the number of cells which fulfill the criteria
CRITERIA The criteria on the basis of which , cells will be counted.
NOTE* CRITERIA NEEDS TO BE GIVEN IN THE “”.
Let us take a mixed data consisting of numbers, dates, numbers as text and text etc. so that we can check the working of function COUNTIF by giving different criteria.
The data used is shown below.
We’ll find out the cells which has value LESS THAN 20
The cells which has value less than 12 OR greater than 400.
And finally using a wildcard will find the words which has RE at the end as suffix.
1. Place the cursor in the cell and type the following function
CRITERIA 1: <20
2. Press ENTER, the answer will appear as 2.
3. Place the cursor in the cell and type the following function
CRITERIA 2: <12 or >400
=COUNTIF( C5:C24,“<12”)+COUNTIF ( C5:C24 ,” >400 ” )
*If there are two conditions, its better to make a logic like the one we made. It fulfills the conditions but the condition is single.
4. Press ENTER, the answer will appear as 1 as there is no values less than 12 and only one value greater than 400.
5. Place the cursor in the cell and type the following function
CRITERIA 3: WORDS ENDING WITH “RE” [USING COUNTIF TO USE WILDCARDS IN GOOGLE SHEETS]
6. Press ENTER, the answer will appear as 4.
The answers can be checked manually. We find that all answers are correct.
OTHER WAYS TO REACH THIS ARTICLE
YOU MAY LIKE