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

Round Off Numbers with Excel’s ROUND Formula What does it do?Rounds a number to the nearest decimal based on your specified number of digitsFormula breakdown:=ROUND(number, num_digits)What it means:=ROUND(the number, number of decimal places to round off)Ever had the need to round off numbers? ...
Extract First Name From Full Name in Excel There were countless times when I had a list of full names, and all I needed was the First Name. It would be time-consuming to manually get the first names one by one. Thank goodness there are formulas to make my life easier!In Excel, it's very easy to do that with the LEFT a...
Extract Last Name 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 stringFormula breakdown:=REPLACE(old_text, start_num, num_chars, new_text)What it means:=REPLACE(this cell, starting from this number, all the ...
Get the Average of Values with Excel’s AVERA... What does it do?Gives you the average of a group of valuesFormula breakdown:=AVERAGE(number1, number2...)What it means:=AVERAGE(the numbers you want to average) There are times when you have to get the average of your values in your Excel work...