COUNTIFS in Excel

Excel COUNTIFS (Table of Contents)

• Introduction to COUNTIFS in Excel
• How to Use COUNTIFS in Excel?

Introduction to COUNTIFS in Excel

‘COUNTIFS’ is a statistical function in Excel that is used to count cells that meet multiple criteria. The criteria could be in the form of a date, text, numbers, expression, cell reference or formula. This function applies the mentioned criteria to cells across multiple ranges and returns the count number of times the criteria are met.

COUNTIFS Function Syntax:

COUNTIFS Function Arguments:

• range1: Required, represents the first range of cells that we wish to evaluate if it meets the criteria.
• criteria1: Required, represents the condition or criteria to be checked/tested against each value of range1.
• range2: Optional, represents the second range of cells that we wish to evaluate if it meets the criteria.
• criteria2: Required, represents the condition or criteria to be checked/tested against each value of range2.

A maximum of 127 pairs of criteria and range can be entered in the function.

The number of rows and columns of each additional range provided should be the same as the first range. If there is a mismatch in the length of ranges or when the parameter supplied as criteria is a text string that is greater than 255 characters, then the function returns a #VALUE! error.

How to Use COUNTIFS in Excel?

Let’s understand how to use the COUNTIFS in Excel with a few examples.

Example #1 – COUNTIFS Formula with Multiple Criteria

Let’s say we have the result status of a class of students, and we wish to find a count of students who have passed as well as scored ‘First Division’ marks.

The ‘Pass/Fail’ status of students is stored in column B and the result status according to marks i.e. if it is ‘First Division’ or not, is stored in column C. Then the following formula tells Excel to return count of students who have passed as well as scored ‘First Division’ marks.

So we can see in the above screenshot that the COUNTIFS formula counts the number of students who meet both the given conditions/criteria, i.e. students who have passed as well as scored ‘First Division’ marks. The students corresponding to below-highlighted cells will be counted to give a total count of 2, as they have passed as well as scored ‘First Division’ marks.

Example #2 – COUNTIFS Formula with the Same Criteria

Now let’s say we have scores/marks of a class of students in the first two quarters, and we wish to find the count of students who scored more than 85 marks in both the quarters. The scores of students in Quarter1 are stored in column B and scores of students in Quarter2 are stored in column C. Then the following formula tells Excel to return a count of students who scored more than 85 marks in both the quarters.

So we can see in the above screenshot that the COUNTIFS formula counts the number of students who have scored more than 85 marks in both the quarters. Students corresponding to below-highlighted cells will be counted.

Example #3 – Count Cells whose Value lie between Two Numbers

Let’s say we have two columns containing item numbers and their quantities present in a store. Now if we wish to find out the count of those cells or items whose quantity is between 10 and 20 (not including 10 and 20), then we can use the COUNTIFS function as below.

So we can see in the above screenshot that the formula counts those cells or items whose quantity is between 10 and 20. Items corresponding to below-highlighted values will be counted to give a total count of 2.

Example #4 – Criteria is a Cell Reference

Let’s say we have result status (Pass/Fail) of a class of students, and their final marks in a subject. Now if we wish to find a count of those students who have passed as well as scored more than 85 marks, then, in this case, we use the formula as shown in the below screenshot.

So we can see in the above screenshot that in the criteria, cell reference is passed rather than supplying direct value. Concatenate (‘&’) operator is used between ‘>’ operator and cell reference address.

The students corresponding to below-highlighted cells will be counted to give a total count of 5, as they meet both the conditions.

Things to Remember About COUNTIFS in Excel

• COUNTIFS is useful in cases where we wish to apply different criteria on one or more ranges, whereas COUNTIF is useful in cases where we wish to apply a single criteria on one range.
• The wildcards which we can use in criteria associated with COUNTIFS function are: ‘?’ to match a single character, and ‘*’ to match the sequence of characters.
• If we wish to find actual or literal asterisk or question mark or asterisk in the range supplied, then we can use a tilde (~) in front of these wildcards ( ~*, ~?).
• If the argument provided as ‘criteria’ to the COUNTIFS function is non-numeric, then it must be enclosed in double-quotes.
• Cells that meet all the specified conditions are only counted by COUNTIFS function.
• Both contiguous and non-contiguous ranges are allowed in the function.
• We should try to use absolute cell references in criteria and range parameters so that the formula is not broken when it is copied to other cells.
• COUNTIFS can also be used as a worksheet function in Excel. COUNTIFS function returns a numeric value. COUNTIFS function is not case sensitive in case of text criteria.
• If the argument provided as ‘criteria’ to the function is a blank cell, then the function treats it like a zero value.
• The relational operators that can be used in expression criteria are:

• Less than operator: ‘<’
• Greater than operator: ‘>’
• Less than or Equal to operator: ‘<=’
• Greater than or Equal to operator: ‘>=’
• Equal to operator: ‘=’
• Not Equal to operator: ‘<>’
• Concatenate operator: ‘&’

Recommended Articles

This is a guide to COUNTIFS in Excel. Here we discuss how to use COUNTIFS in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. Count Characters in Excel
2. Excel SUMIFS with Dates
3. KPI Dashboard in Excel
4. Combo Chart in Excel