In this post, I wanted to just explain a little better the difference between COUNTIF and COUNTIFS functions. Both functions will count items for you based on criteria you set. However COUNTIF only checks 1 criteria. COUNTIFS checks multiple criteria.
Let me explain by showing an example.
Say you have this:
We have a list of people with the hours they slept and minutes they exercised.
So what if we wanted to count how many people slept more than 8 hours.
The COUNTIF function would do that because we are only checking one criteria. So we would insert that function:
The Range would be the range of cells we want to check.
The Criteria is the test we need Excel to perform. In this case it is “>8” so it will check each item if it is more than 8 hours.
The final result we get is 2. There’s only 2 people with more than 8 hours of sleep. Yes there are a few with exactly 8. But we want to check for more.
But what if we wanted to check how many people got more than 8 hours of sleep AND 30 minutes of exercise?
This is where we want to use COUNTIFS. The main difference between these two is that COUNTIFS checks multiple criteria, while COUNTIF checks 1 criteria.
So let’s do the COUNTIFS function for this one:
Here we enter in both ranges we want to check and both criterias associated with them. So the first part looks just like our first COUNTIF function. However the second part lists our next range of minutes of exercise and the second criteria checks if that number is over 30.
So in total it checks both criteria and comes back with a result of 1. There is only one person who has over 8 hours of sleep and 30 minutes of exercise.
Here’s our final table:
So that ends our comparison of COUNTIF and COUNTIFS. I hope this helps clarify the difference to you!
***If you would like more information about Excel functions, check out our program called Master Excel Functions at http://excelbyjoe.com/excel-software-solutions/master-excel-functions/ It has over 4 hours of videos and 114 page ebook to thoroughly explain Excel functions and formulas.