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

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Share on Google+

Related Posts

CountBlank Formula in Excel What does it do?Counts the number of cells that are blankFormula breakdown:=COUNTBLANK(range)What it means:=COUNTBLANK(range of cells to check)Do you have a scenario where you want to count the number of cells that are blank in your Excel data?If y...
Getting the remainder with Excel’s MOD Formu... What does it do?Gives you the remainder after dividing one number with anotherFormula breakdown:=MOD(number1, number2)What it means:=MOD(a number, divided by this number)In Excel it is very easy to divide two numbers.But how about if you need to ge...
RANDBETWEEN Function for Excel Dates What does it do?Generates random dates, in between a start date and an end dateFormula breakdown:=RANDBETWEEN(bottom, top)What it means:=RANDBETWEEN(starting date for random date generation, end date for random date generation) If you need to ...
Division Formula in Excel What does it do?Divides two numbersFormula breakdown:=number1 / number2What it means:=the number being divided / the number you are dividing byIn Excel dividing numbers together is really easy!The Division Formula is done through the use of the div...