Adding 2 or more COUNTIF formulas
Did you know that you can add the result of multiple COUNTIF formulas? COUNTIF is normally used on it’s own as several entries and then added together (SUM) to get the total. But it can actually be done in a single step!
I've covered COUNTIF in a previous post, "COUNTIF" so I’m going to get straight into it. But before I do, here’s a quick reminder of the COUNTIF formula:
Excel Formula: =COUNTIF(range,criteria)
Example
I have some crime data for that shows me the list of offences committed in January and February.
I want to know the NUMBER (count) of REGULATORY DRIVING OFFENCES (if / condition) AND the NUMBER (count) of HOMICIDE AND RELATED OFFENCES. The crucial takeaway here is the word "and".
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 Made Easy
AND
Formula Applied
Putting the above references into the Excel formula, it will be:
=COUNTIF(C:C,“Regulatory driving offences”) + COUNTIF(C:C,“Homicide and related offences”)
You can download the file and give it a go here: Crime Statistics
Notes: Categorical data and images of this tutorial are based on the following:
Countif hands image from 123RF
List of council areas from State Government of Victoria, Victorian Local Councils - http://www.vic.gov.au/government-economy/local-councils/victorian-local-councils.html
List of offences and offence category from Crime Statistics Agency (CSA) - https://www.crimestatistics.vic.gov.au/
All other figures and data are my own and is for illustrative purposes only.
Comments