Excel COUNTIF Function With Multiple Criteria

Excel COUNTIF function

 

Excel COUNTIF function

Excel COUNTIF function is the combination of COUNT and IF function.

 

Excel COUNTIF function – How to define

“Excel COUNTIF  function helps us to find a result in a range of cells or dataset if there is any condition or criteria is provided.”

Syntax of Excel COUNTIF function

=COUNTIF (range, criteria)

range – dataset or range where our result is located.

criteria – this is a condition must be met when to find a result.

 

Important note

When working with Excel COUNTIF function, always use one criteria with one range.

 

COUNTIF function, gives a result with a numeric value, like (0,1,2,3, . . .) so we can use more “COUNTIF” function with addition (+) or subtraction (-) sign.

 

We can apply two or more criteria in a single range but one criteria with one range and then add or subtract them. See the rule one (1).

Topics for Excel COUNTIF function

  • The frequency of a “name” in a range
  • Count before or after a particular date
  • How to find a sales target met or not
  • COUNTIF + COUNTIF how to use
  • How to find duplicate entries
  • How to find blank and no blank
  • With a wildcard character (? *, ~)
  • Sorting the data in ascending order

The frequency of a “name” in a range

 

COUNTIF names

 

Here in the above image, we can see that the frequency of “George” in “First Name” Column is more than one.

 

Suppose we want to know that how many “George” appear in column “A” or “First Name” column. Then apply the formula of Excel COUNTIF function as following way:-  =COUNTIF(range, criteria).

 

Can also write it like =COUNTIF(range of all name in a column “($A$2:$A$11)”, criteria “(George)”. So our formula is =COUNTIF($A$2:$A$11,”George”).

 

As we already told in this blog that “COUNTIF” always produce a result in a numeric form. So here we will get the answer in the number form, and our result is “2”.

There are “2” numbers of “George” in the Name column in the above-given table.

 

Count before or after a particular date

 

Now come to the second point, where we have to find out how many employees are there who joined this company before or after a particular date.

 

COUNTIF With Date

 

Our data will remain the same, and our “range” area will be changed from “name range” to “date range” because we are looking for a date range. So our range will be “C2: C11” instead of “A2: A11”.

 

So our formula would be in this case “=COUNTIF (“range of date area”(“$C$2:$C$11”), criteria (“>11-May-14”).  

Here we choose our criteria as “>11-May-14”, it will show us all the number of employees who joined the organization after “>11-May-14” date.

 

The final formula is =COUNTIF ($C$2:$C$11, “>11-May-14”) and our result is “4”.

 

And same way we can find the joining date before a particular date by changing the formula as =COUNTIF ($C$2:$C$11, “<08-Aug-14”) where we have applied “< less than” sign instead of “>greater than”.

 

How to find a Sales target met or not.

 

Next topic is, how many employees have met their sales target which is assigned to them. Here “Sales” is our criteria range.

 

COUNTIF Sales Target

 

First of all, select the range where the sales figure is appearing, which is in column “D” of the data-set.

 

So for those who met their sales target, the formula will be “=COUNTIF(“D2: D11”, “>5000”). This formula tells that how many employees have met their sales target.

 

COUNTIF + COUNTIF how to use

 

Next one is combining “COUNTIF +COUNTIF” in one formula. We can apply two or more COUNTIF function in a single equation, as we know that it will produce a number value. 

 

Here we will find the total number of “Sales” and “Purchasing” departments in the data-set. Apply the below formula to find out.

 

Excel COUNTIF + COUNTIF Function

 

Our first formula will find the “how many people are in the “Sales” department whereas our second COUNTIF function will find out how many of them are in the “Purchase” department. 

 

How to find duplicate entries

 

Now how to find duplicate entries in a column. In the below table we have “Names” in a column “C” and column “D” has the formula. Apply “COUNTIF” formula to find duplicate entries, which gives result in the form of TRUE/FALSE. “TRUE” are duplicate entries and “FALSE” is unique or not duplicate. See below:-

 

Excel COUNTIF function for Duplicate

 

Here is the formula applied, “=COUNTIF($A$34:$A$43, A34)>1.

Apply the formula in the 2nd column; it will give a result as TRUE/FALSE. TRUE is, duplicate existing whereas,  FALSE is unique existing. Drag the formula end of the dataset and done.

 

How to find blank and no blank

 

Now the next topic is how to find a cell is blank or not blank. As we can see in the below-given image, our first column has some text values (names). Some cells are empty or not showing any amount or text matter.

 

Here we will apply the formula to find a blank cell. See below formula: –
“=COUNTIF (D2,””)”
 
This formula will give us the result in the form or “0” and “1”. Earlier in this blog, we learned that “COUNTIF” function provides a result in a numerical value, like (0,1,2,3,4……).
After applying the formula “=COUNTIF (D2,””)”, if any cell has a value it will count as “1” or if there is no value it will count as “0”. Now apply the “IF” formula to exchange “0” with “Not Blank” and “1” with “Blank”.

How this formula works – it will count a cell as “1” if it has a blank space or counts “0” if there is no blank space. (As we are counting empty cell only). See below image:-

 

Excel COUNTIF Blank Cell

With wildcard character (?, *)

 

How to use wildcard characters, “Asterisk (*) and question mark (?)” with Excel COUNTIF function. 

Asterisk (*) – How to use

                            (*e) = any numbers of character before character “e”

                            (e*) = any numbers of character after character “e”

Question mark (?) – How to use

                           (??) =one (?) sign for one character and two sign (?) for two and so on…

 

In a below-given image, we want to count the words which have its last characters as “e”. Apply Excel COUNTIF function as “=COUNTIF (A2: A11,”*e”)”. And see the image:-

 

Excel COUNTIF with Wildcard character

 

 

The formula will find words, containing the last character as “e”. It does not matter how many characters it has before it.

 

Similarly, we can find the result as “how many words contain its first character as (J) “
 
So, in this case, we will use formula as =COUNTIF(A2: A11,”J*”).
 
The formula will find out the words containing “J” as the initial letter. It may contain any number of character after it.
First of all, we will type the character which we are looking for then type (*) asterisk character.
Excel COUNTIF with wildcard

 

In the below-given table, we are looking for a count of the words which has its three characters as “Joh” and rest characters we do not know.

 

So with the help of Excel COUNTIF function, we can find the total number of a name which has the first three number as “Joh”. See the formula in the below-given image:-

 

Excel COUNTIF wildcard function

 

Similarly, we can solve the query for the name whose first three characters we do not know, but the last three characters are “rge”. The solution is in the below-given image:-

 

Excel COUNTIF wildcard character

Sorting the Data in ascending order

 

Now, learn how to sort Excel data in ascending order with the help of Excel COUNTIF function.

 

Is it possible? Let’s try

 

Suppose that we have a name list and we want to sort the table in the ascending orders. Like (1,2,3,4… so on) or in alphabetic ascending orders.

 

We have names in column “A” and apply the formula in column “B”. Write a formula in cell one of column B and drag it to the last row of the name list. The sequence number of every name appears in the second column. See image below:-

 

Excel COUNTIF sort ascending

 

These are the few examples of Excel COUNTIF function. There are many more another examples but could not explain in a single blog. Using these example is to make an overview of the Excel COUNTIF function. Indeed a most useful function.

 

Please feel free to make a comment or suggestion for this blog. I would feel grateful if someone shares something.

 

 

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.