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:

oztraining_728x90

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...
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...
Change Phone Area Codes with Excel’s REPLACE Formu... What does it do? Replaces part of a text string, based on the number of characters you specify, with a different text string Formula breakdown: =REPLACE(old_text, start_num, num_chars, new_text) What it means: =REPLACE(this cell, starting from this number, all the ...
Cleaning Data with Excel’s REPLACE Formula What does it do? Replaces part of a text string, based on the number of characters you specify, with a different text string Formula breakdown: =REPLACE(old_text, start_num, num_chars, new_text) What it means: =REPLACE(this cell, starting from this number, all the ...