What does it do?

Counts the number of cells that matches your specified conditions

Formula breakdown:

=COUNTIFS(range1, criteria1, [range2], [criteria2], …)

What it means:

=COUNTIFS(range of cells to check1, condition to check against1, [range of cells to check2], [condition to check against2], …)


Do you have a scenario where you want to count the number of cells that match specific conditions?

I’m sure you do!  There is a simple way to count this with Excel’s COUNTIFS formula!

This is very similar to the CountIf Formula! The only difference is it allows you to add even more conditions as needed…That’s POWEFUL!

The COUNTIFS formula is very flexible indeed, so let us try to count the following from our Excel worksheet:

  • Number of times John got more than 10,000 sales
  • Number of times Kim got more than 18,000 sales

CountIfs Formula in Excel

I explain how you can do this below:

DOWNLOAD EXCEL WORKBOOK

STEP 1: Let us target the first question: How many times John got more than 10,000 sales?

We need to enter the COUNTIFS function in a blank cell:

=COUNTIFS(

CountIfs Formula in Excel

 

STEP 2: The COUNTIFS arguments:

range1, criteria1

What is our first condition?

We want to find the names that match “John”

=COUNTIFS(A9:A13, “John”,

CountIfs Formula in Excel

range2, criteria2

What is our second condition?

We want to find sales that are more than 10,000

=COUNTIFS(A9:A13, “John”, C9:C13, “>10000”)

CountIfs Formula in Excel

You now have your count of 2!

CountIfs Formula in Excel

 

STEP 3: Now let us try doing the same for Kim!

range1, criteria1

What is our first condition?

We want to find the names that match “Kim”

=COUNTIFS(A9:A13, “Kim”,

CountIfs Formula in Excel

range2, criteria2

What is our second condition?

We want to find the sales that are more than 18,000

=COUNTIFS(A9:A13, “Kim”, C9:C13, “>18000”)

CountIfs Formula in Excel

You now have your count of 1!

CountIfs Formula in Excel

You can have more than 2 conditions in the COUNTIFS formula, so go crazy with the COUNTIFS!

CountIfs Formula in Excel

CountIfs Formula in Excel

HELPFUL RESOURCE:

How to Combine VLOOKUP and IFERROR to Replace the #N/A Error in Excel

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Share on Google+

Google+

Related Posts

CountA Formula in Excel What does it do? Counts the number of cells that are non-blank/non-empty (including empty text "") Formula breakdown: =COUNTA(value1, , ...) What it means: =COUNTA(value or range of cells to check, , ...) Do you have a scenario where you want to count the nu...
VLOOKUP with Multiple Criteria in Excel ‘VLOOKUP’ is now so common that everyone who has ever been working with Excel has heard of it. Not necessarily because they know how to use it, but because some savvy Excel-user always talks about it at the office. Advanced users have seen the use of adding more than one...
Cleaning Data with Excel’s SUBSTITUTE Formul... What does it do? Substitutes new_text for old_text in a text string. Formula breakdown: =SUBSTITUTE(text, old_text, new_text, ) What it means: =SUBSTITUTE(This cell, By this text character, To this text character, ) When you needed to replace a specific text...
Division Formula in Excel What does it do? Divides two numbers Formula breakdown: =number1 / number2 What it means: =the number being divided / the number you are dividing by In Excel dividing numbers together is really easy! The Division Formula is done through the use of the div...