how to use excel countifs function

Quick Intro

Use Excel COUNTIFS Function to count cells which meet multiple criteria. In simple words, you can check how many cells are there in a range that meet multiple criteria that you have specified.

Syntax

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

  • criteria_range1 – The range of cells which you want to evaluate using criteria1.
  • criteria1 – The criteria which you want to evaluate for criteria_range1.
  • [criteria_range2] – The range of cells which you want to evaluate using criteria1.
  • [criteria2] – The criteria which you want to evaluate for criteria_range1.

More Information on Excel COUNTIFS Function

  • All criteria ranges must have the same number of rows. For example, If criteria_range1 has 10 rows then criteria_range2 must have 10 rows.
  • You can use a text, a number, a cell reference, or a formula in criteria.
  • You can evaluate up to 127 criteria ranges using COUNTIFS Function.
  • Criteria which is a text or a logical operator should be enclosed in double quotes.
  • You can also use wildcard characters in COUNTIFS Function.
  • If criteria is a reference to a cell which is empty, the function will treat it as 0.
  • COUNTIFS is not case sensitive.

Examples

In the below example, I have used COUNTIFS to count females whose are above 25 years. I have specified two criteria for evaluation, one is female and other is a greater than operator to count cells with a number which is greater than 25.

Multiple Criteria Cells Count With Excel COUNTIFS Function

In the below example, I have used the asterisk in one criterion and greater than operator in another one to count the number of the person whose name is starting with alphabet A and whose age is more than 25.

Using-Wildcard-Characters-With-Excel-COUNTIFS-Function

Sample File

download sample file to learn more about this tips

What’s Next?

To learn more about Excel COUNTIFS Function you can check Microsoft’s Help Section. And, if you have a unique idea to use it, I would love to hear from you.

There is also you have COUNTIF function which is highly useful. Apart from this, I have a list of excel functions and some real life formulas examples.