COUNTIFS
What is it?
“The COUNTIFS function applies criteria to cells across multiple ranges and counts the number of times all criteria are met” - Microsoft Office Support 2017
The difference between COUNTIF and COUNTIFS is the number of criteria that you would like the cells to meet. COUNTIF meets a criterion while COUNTIFS meets multiple criteria. If you've missed my post on COUNTIF, click here.
Excel Formula: =COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2...)
Formula Made Easy
Example 1
I have some crime data for that shows me the list of offences committed in January and February (same data as the one in ADDING 2 OR MORE COUNTIF FORMULAS).
I want to know the NUMBER (count) of REGULATORY DRIVING OFFENCES (1st criteria) that were committed in the LODDON SHIRE (2nd criteria) Council Area.
Based on the above photo, if I were to fill in the cell references into my Formula Made Easy table, it will look like:
Formula Applied
Putting the above references into the Excel formula, it will be:
=COUNTIFS(C:C,“Regulatory driving offences”,A:A,“Loddon Shire”)
Alternatively, I can also replace “Regulatory driving offences” & “Loddon Shire” with cell references: =COUNTIFS(C:C,C2,A:A,A2). It also doesn’t matter if I put column A as my first criteria range and “Loddon Shire” as my first criteria - I’ll still get the same result.
Example 2
Based on the same crime data containing the list of offences committed in January and February, I want to know the NUMBER (count) of OFFENCES that were committed in the FIRST WEEK OF JANUARY (04/01/2016 - 10/01/2016).
Based on the above photo in Example 1, if I were to fill in the cell references into my Formula Made Easy table, it will look like:
Formula Applied
Putting the above references into the Excel formula, it will be:
=COUNTIFS(D:D,”>=”&”04/01/2016”, D:D,”<=”&”10/01/2016”)
Note that after “>=” (greater than or equal to) or “<=” (less than or equal to), you need to put an ‘&’ symbol.
Instead of typing the dates in, I could have replaced it with a cell containing those dates.
Download the file and give it a go here: Crime Statistics