Excel Countif and Countifs are a must function for any Excel user. If you want to count the number of cells in a clever way and be able to quickly adjust that way easily, these functions will be very useful for you.
Excel Countif , Countifs example
In order to demonstrate the capabilities of these functions I will use a US cities and states database I downloaded from the internet. The database has ~900 rows. This is a small scale print screen of the database :
In column K you can see an example of Countif. Based on the US database above, I wanted to count the number of the cities per each state. Countif only allows one condition to be checked for the counting, countifs allow you to define many conditions. In column L you can see an example of countifs with many conditions – not only am I counting the number of cities per state,I am only including cities with a name shorter than 10 characters. In column M I am also limiting the search to cities where the population is more than 300K.
Excel Countif , Countifs code breakdown for easy copying
Column K = Countif($C:$C,$J14)
Column L = Countifs($C:$C,$J14,$H:$H,”<10″)
Column M = Countifs($C:$C,$J14,$H:$H,”<10″,$F:$F,”>”&M$2)
Detailed explanation of the function Countif , Countifs
1st argument – Range where the criteria will be checked
2nd argument – Value to be checked in the range
For countifs – each new argument couple will be the range and value based on the 2nd and 3rd example.
Countif and Countifs are great functions that should be part of your Excel tool box. Below you will find more valuable functions to use :