As a Microsoft Excel user you must have been faced with a task where you needed to count how many records you have in your dataset; that is easy. What gets tricky for most people is needing to count the records that meet multiple criteria.
In this post, I am doing to demonstrate how to count records that meet multiple criteria and use date criteria as it is a fairly common real world use case.
Let us consider a sales analyst working on a sales record of product purchased and delivered to customers by a due date and he is told to provide the number of purchases made after January 4th, 2021 and were delivered after February 4th, 2021.
So, we will use the COUNTIFS formula as it allows us to do a count of records that meet more than one criteria.
In this case there are two criteria.
- purchases made after 4 January 2021, and
- delivery made after 4 February 2021
The syntax for COUNTIFS is
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
You provide the criteria range (the field/column you want to check for specific condition/criterion), then the criterion (criteria1) to check for. Then repeat for any other additional conditions to check for. There must be at least one condition (meaning at least one criteria range and one criterion). And you can have as many as 127 criteria pair.
So next time you are trying to extract some insights from your dataset using conditions, you can add COUNTIFS to your toolbox to achieve some valuable analysis.
Hope you found this helpful!
Do not forget to check out our training courses: https://www.urbizedge.com