COUNTIF
COUNTIF is “one of Excel’s statistical functions, to count the number of cells that meet a criterion” - Microsoft Support Office 2017. In layman’s terms, it means “COUNT the NUMBER of cells, IF it contains XYZ (text, number, etc.)”. The key word here is criterion, referring to only one criteria.
Excel Formula: =COUNTIF(range,criteria)
Formula Made Easy
Examples
I have some crime data for January. It contains the following:
Council Area - which area the offence occurred
Offence
Offence Category
Date of Offence
Gender
I want to know the NUMBER (count) of offences that were committed by EACH GENDER (if / condition).
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:
Formula Applied
Putting the above references into the Excel formula, it will be:
=COUNTIF(E:E, “Male”)
=COUNTIF(E:E, “Female”)
So far so good. What if I want to know the NUMBER (count) of offences occurred in ALL COUNCIL AREAS, EXCEPT those in WHITEHORSE CITY? The crucial takeaway here is the word except.
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:
Formula Applied
Putting the above references into the Excel formula, it will be:
=COUNTIF(A2:A74, “<>Whitehorse City”)
Note <> means “exclude, except”.
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