How to Use Multiple Criteria in Excel Functions COUNTIF and COUNTIFS

There are so many functions in Excel wherein a user has to specify various or just one criterion to get a result.

For example, whenever you want to count the cells based on more than one criteria, you can have the Excel functions COUNTIFS or COUNTIF used.

Our tutorial will cover the various ways you can use the multiple or single criteria in Excel’s COUNTIFS and COUNTIF function.

As we will primarily focus on the functions COUNTIFS and COUNTIF, you can also have these examples used in other functions of Excel that have various criteria used as inputs.

These are AVERAGEIFS, AVERAGEIF, SUMIFS, and SUMIF.

Our Introduction to the COUNTIFS and COUNTIF Functions in Excel

First, we need to have a grip on how to use Excel’s COUNTIFS and COUNTIF functions.

Excel COUNTIF Function that takes a Single Criteria

It would be best to use the Excel COUNTIF function for situations wherein you have to count cells based on one criterion.

Whenever there are multiple criteria that you want to base the count on, use the function COUNTIFS

Syntax

=COUNTIF(range, criteria)

Input Arguments

  • Criteria – you need to evaluate the criteria against a range of cells to count the cell.
  • Range – the cell range that you want to count.

Excel COUNTIFS Function that takes Multiple Criteria

This function of Excel COUNTIFS is ideal for situations when you desire to have cells counted based on multiple criteria.

Syntax

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

Input Arguments

  • criteria1 – this is the criteria you want to have criteria_range1 evaluated to determine which cells would be counted.
  • criteria_range1 – here is the range of cells that you would like to evaluate against criteria1.
  • [criteria2] – this is the criteria that you want to evaluate for criteria_range2 so you can determine what cells to count.
  • [criteria_range2] – here is the range of cells that you want to evaluate against criteria2.

Let us check out some of the examples in using various criteria in Excel’s COUNTIF functions.

Using NUMBER Criteria in Excel COUNTIF Functions

#1 Count the Cells whenever the Criteria has the Value Greater Than

If you want to count the cells that have a value that is greater than a specific value, you can use the operator greater than (“>”). You can either have it used as a cell reference with the criteria or directly in the formula.

In Excel, whenever you have an operator used in the criteria, it needs to be within the double-quotes. For example, whenever the criteria are more than 10, you need to have “>10” entered as the criteria.

Here is the formula:

=COUNTIF($B$2:$B$11,”>10″)

It can also be possible to have the criteria used in the cell and the cell reference used as the criteria. For this, you don’t need to have the criteria placed in double-quotes:

=COUNTIF($B$2:$B$11,D3)

Sometimes, you may want to include criteria in a cell.

However, you don’t want the operator to be with it.

For example, you may wish to cell D3 only to have the number 10 that doesn’t include >10.

For this case, you have to make a criteria argument that combines the cell reference and operator:

=COUNTIF($B$2:$B$11,”>” &D3)

Take note that whenever an operator and cell reference are combined, the operator will always be in double-quotes.

Ampersand (&) will join the cell reference and the operator.

#2 Whenever the Criteria has a Value EQUAL to, Count the Cells

Suppose you want to have the count of cells wherein a specified value is equal to the criteria argument.

In that case, you can either use the cell reference with the criteria or have the criteria entered directly. 

Here is an example where you can count the cells that have the number 9.

It means that the criteria argument has a value equal to 9. Here is the formula:

=COUNTIF($B$2:$B$11,D3)

In this example, cell D3 has the criteria.

You can also have the criteria entered directly into the formula. As an example, you may use:

=COUNTIF($B$2:$B$11,9)

#3 Between Two Values, Count the Cells with Multiple Criteria

In between two values, get a count of the values using the COUNTIF function’s multiple criteria.

Do this with these two methods:

METHOD 1:

Use two COUNTIF functions

Whenever you have multiple criteria, either make a combination of the COUNTIF functions or use COUNTIFS. You can do the same thing by using the formula below:

=COUNTIF($B$2:$B$11,”>4″)-COUNTIF($B$2:$B$11,”>8″)

Here in this formula, you first need to search for the number of cells with a value greater than 5.

Use a value that is greater than 10 to subtract the count of cells.

It will provide you with 5 as a result: the number of cells with values that have more than 5 and less than that is equal to 10.

Whenever you want both 5 and 10 included in the formula, have this formula used instead:

=COUNTIF($B$2:$B$11,” >=5″)-COUNTIF($B$2:$B$11,”>10″)

In case you want the formula to have both ‘10’ and ‘5’ excluded from the count, have this formula used:

=COUNTIF($B$2:$B$11,”>=5″)-COUNTIF($B$2:$B$11,”>10″)-COUNTIF($B$2:$B$11,10)

Use cell references and have these criteria in cells. It can be possible to combine the cells references and operators.

METHOD 2: Use of the COUNTIFS function

Handle multiple criteria with the COUNTIFS function because arguments and counts of the cell only happen when every criterion is TRUE.

If there are a couple of specified values (like 5 and 10), you can use this in the function COUNTIFS:

=COUNTIFS($B$2:$B$11,”>5″,$B$2:$B$11,” <10″)

Take note that this formula doesn’t count the cells that have 5 or 10.

Whenever you want these cells included, use the operators less than equal to (<=) and greater than equal to (>+=).

Here is the formula for it:

=COUNTIFS($B$2:$B$11,”>=5″,$B$2:$B$11,”<=10″) 

It can also be possible for you to use cell reference as the criteria and have the criteria in the cells.

Whenever this is the case, you don’t have to place the criteria in double-quotes:

It can also be possible for you to use a combination of cell operators and references wherein the operator gets directly entered into the formula.

As you combine a cell reference and an operator, the operator will always be in double-quotes. The ampersand (&) will join the cell reference and the operator.

#4 Count the Cells whenever the Criteria has a LESS THAN Value

Get the cell count with a value that has less than a specified value by using the operator less than (“< “).

You can either have it used as a cell reference with criteria or directly in the formula.

Every time an operator is used in Excel criteria, you have to place it in double-quotes.

As an example, whenever a criterion is a number that must be less than 5, you need to have “<5” entered as the criteria:

=COUNTIF($B$2:$B$11,” <5″)

It can also be possible to put the criteria in a cell and have the cell reference used as the criteria.

Whenever this happens, you don’t have to place the criteria in double-quotes:

=COUNTIF($B$2:$B$11,D3)

There may be a case when you want to have the criteria in the cell, but you don’t want it to be with the operator.

For example, you may want the number 5 and not <5 to be in cell D3.

Whenever that is the case, you have to make a criteria argument that combines the cell reference and the operator

For this case, you have to make a criteria argument that combines the cell reference and the operator:

=COUNTIF($B$2:$B$11,” < “&D3)

Take note that the operator will always be in double-quotes whenever a cell reference combines with an operator.

The ampersand (&) will join the cell reference and the operator.

Using TEXT Criteria in Excel Functions

#1 Count the Cells whenever the Criteria is NOT EQUAL to the Specified Text

It can be possible to count the cells that don’t have a specified text, just like in other examples.

If you want to do this, you have to use the operator not equal.

Whenever you want to have all of the cells that don’t have the name JOE counted, use this formula for it:

=COUNTIF($B$2:$B$11,” <>Joe”)

It can also be possible to have the cell reference used as a criterion and have the criteria in the cell.

Whenever this is the case, you don’t need to put double quotes in the criteria:

=COUNTIF($B$2:$B$11,E3)

There are times when you want criteria to be in a cell but without the operator.

For example, you can have cell D3 to include the name Joe and not <> Joe.

For this case, you have to make a criteria argument that combines the cell reference and operator:

=COUNTIF($B$2:$B$11,” <>” &E3)

Every time a cell reference and operator combine, the operator will always be in double-quotes.

The ampersand (&) will join the cell reference and operator.

#2 Have cells counted whenever the Criteria is EQUAL to a Specified Text

Use the Text as a criterion to count the cells with an exact match of the Text specified.

As an example, in the shown data set, if you want to count every cell that has the name Joe, use this formula: 

=COUNTIF($B$2:$B$11,” Joe”)

Being a text string, you have to place double quotes in the text criteria.

You may even allow the cell to have the criteria so you can use it as a cell reference: 

=COUNTIF($B$2:$B$11,E3)

Take note that you can still get incorrect results whenever the criteria range or criteria has trailing or leading spaces.

Make sure to have the data cleaned before you use any of these formulas.

Use of the DATE Criteria in Excel’s COUNTIF and COUNTIFS Functions

Excel allows time and date to get stored as numbers so you can use it similarly to how we use numbers.

#1 Have cells counted whenever the Criteria is AFTER or BEFORE a Specified Date

Count the cells with the date after or before a specific date.

Do this by using the operators greater than or less than.

As an example, whenever you want to have all the cells with a date after September 02, 2015, counted, have this formula used:

=COUNTIF($A$2:$A$11,”>”&DATE(2015,9,2))

You may also count how many cells before a specific date.

In the counting, if you want the date included, use the operator ‘equal to’ with the operator ‘less than/greater than.’

It can also be possible for you to use a cell reference that has a date.

Whenever this happens, you have to combine the date with the operator within double-quotes using the ampersand (&).

Here is an example

=COUNTIF($A$2:$A$11,”>” &F3)

#2 Whenever a Criteria Becomes EQUAL to a Specific Date, Count the Cells

Get the cell count with a specified date by using the equal to an operator (+) with the date.

If you want to use this date, we recommend using the DATE function because it removes the error possibility in the date value.

As an example, whenever you want to have the date September 1, 2015, used, make use of the DATE function like what is shown here:

=DATE(2015,9,1)

With this formula, even if there are regional differences, it would still return the same date.

For example, 01-09-2015 will be January 09, 2015, as per the UK date syntax and September 1, 2015, based on the US date syntax.

Yet, this formula will always have September 1, 2015, returned.

If you need to count how many cells have the date 02-09-2015, use this formula:

=COUNTIF($A$2:$A$11,DATE(2015,9,2))

#3 Count Cells that have Multiple Criteria in Between Two Dates

Get a count of the values between a couple of values using the COUNTIF function’s multiple criteria.

There are two methods you can do this: The two COUNTIF functions and the single COUNTIFS function.

METHOD 1: Using COUNTIF functions

Whenever there are multiple criteria, you may combine two COUNTIF functions or have one COUNTIFS function used.

Here is the formula that can do the trick:

=COUNTIF($A$2:$A$11,”>”&DATE(2015,9,2))-COUNTIF($A$2:$A$11,”>”&DATE(2015,9,7))

In this formula, you need to look for how many cells have the date after September 2.

Then, subtract the number of cells that have dates after September 7. It will provide the result 7.

That is the number of cells with dates after September 2 and before or on September 7.

Use this formula whenever you don’t want the formula to have both September 2 and September 7 counted:

=COUNTIF($A$2:$A$11,”>=”&DATE(2015,9,2))-COUNTIF($A$2:$A$11,”>”&DATE(2015,9,7))

Whenever you want both dates excluded from counting, make use of this formula:

=COUNTIF($A$2:$A$11,”>”&DATE(2015,9,2))-COUNTIF($A$2:$A$11,”>”&DATE(2015,9,7)-COUNTIF($A$2:$A$11,DATE(2015,9,7)))

You can also use the cell references with the operators in double-quotes that are joined with the use of ampersand.

It is also possible to have cells with criteria dates.

METHOD 2: Use of the COUNTIFS function

The COUNTIFS function takes various criteria because of the arguments.

It only counts the cells whenever every criterion is TRUE.

Use this COUNTIFS function to count the cells that have values between a couple of specified dates like September 2 and September 7:

=COUNTIFS($A$2:$A$11,”>”&DATE(2015,9,2),$A$2:$A$11,”<“&DATE(2015,9,7))

This formula doesn’t count the cells that have the specified dates.

Whenever you want these dates included, use the operators less than equal to (<=) and greater than equal to (>=). Here is the formula:

=COUNTIFS($A$2:$A$11,”>=”&DATE(2015,9,2),$A$2:$A$11,”<=”&DATE(2015,9,7))

It can be possible for you to have the cell reference used as criteria and use these dates in a cell.

For this case, you can’t have the operator in the cells with the date.

You will have to add operators in the formula manually and in double-quotes.

Add the cell reference with an & or ampersand. 

=COUNTIFS($A$2:$A$11,”>” &F3,$A$2:$A$11,” < “&G3)

Use of WILDCARD CHARACTERS when it comes to the Criteria of Excel COUNTIF & COUNTIFS Functions

These are three of Excel’s wildcard characters:

  1. Question mark (?) – This represents a single character. As an example, Tr?p can mean Trap or Trip.
  2. Tilde (~) – This is what people use to identify the Text’s wildcard character (*, ?, ~).
  3. Asterisk (*) – Here is a representation of any number of characters. As an example, it can mean expert, excels, excel, etc.

Whenever another inbuilt count function doesn’t work, you can use the COUNTIF function with the wildcard characters so you can count the cells.

As an example, you may have this data set:

Now let’s consider these various examples:

#1 Count Cells that are Non-blank

Whenever you want to consider using the COUNTA function, think about it first, as it may fail you if you try it.

COUNTA can also count a cell with an empty string whenever people only have the apostrophe entered in a cell or with formulas often return as =””.

Cells that have empty strings can appear blank, but they are not, which is why the COUNTA function still counts them.

COUNTA can also count a cell with an empty string that formulas like = often return or whenever people only enter an apostrophe in the cell.

The cells that have empty strings can appear blank but are not. Because of that, the COUNTA function counts them.

Whenever you use the formula =COUNTA(A1:A11), it returns 11 even if it should return 10.

Do this to fix it:

=COUNTIF($A$1:$A$11,”?*”)+COUNT($A$1:$A$11)+SUMPRODUCT(–ISLOGICAL($A$1:$A$11))

Break the formula down so you can understand it:

  • SUMPRODUCT(–ISLOGICAL($A$1:$A$11) – All the cells with the logical values are counted. In this example, 2 is returned.

  • COUNT($A$1:$A$11) – Here, all the cells that have numbers are counted. It returns 3 in this example.

  • COUNTIF($N$8:$N$18,”?*”) – Here is a part of the formula that returns 5. Included here is any cell that consists of a text character in it. A character is represented by A? and * represents any amount of characters. Thus, combining ?* in the criteria will force excel to count the cells with a minimum of a text character in them.

#2 Count Cells that have a specific text

Whenever you want to count every cell with the name of the sales rep starting in J, you can easily achieve this by using the COUNTIF function’s wildcard character.

Here is the formula to use:

=COUNTIFS($C$2:$C$11,”J*”) 

J* is the criteria that specify that the cell’s Text must start with J and has many characters.

Whenever you want to have cells with Alphabet Text counted, make both sides flanked with an asterisk.

For example, whenever you want to count the cells with the alphabet “a” included, use the criteria *a*.

 #3 Count Cells that have Text

If you want to count the cells that have Text in it, you can use a wildcard character asterisk (*) used.

The asterisk can represent any amount of characters, so it can count every cell that has Text in it. Here is the formula for it:

=COUNTIFS($C$2:$C$11,”*”)

Take note that this formula can ignore cells that have logical values, blank cells, and numbers.

However, it would also count the cells that have an empty string (=””) or apostrophe (that appears blank and could return as a part of the formula.

We hope you enjoyed our detailed tutorial about handling cases that have an empty string or apostrophe.

Please share your thoughts with us and leave a comment!

Become Well-Versed in Excel’s CountIF and COUNTIFS Functions with ExcelMaster

Make yourself an Excel expert with us!

Allow yourself to become sufficiently knowledgeable about the Excel CountIF and COUNTIFS Functions with ExcelMaster NOW!