Happy Friday and time for more FORMULA FUN on #FORMULAFRIDAY. This article will use the COUNTIFS Function in Excel to calculate how many people are working at the same time in a retail store. Use this simple solution for a factory or some other place of work where you need to see how many people are working at any one time during the work shift hours. This is based on the clocking in and clocking out time (or scheduled shift times of workers) versus the time of day. Let’s get going and use the COUNTIFS function to assist us with this question.
First, here are the scheduled working times of staff. The solution calculates how many staff are on shift at any given time.
First, here is a quick reminder of the function we are going to use.
The COUNTIFS Syntax.
The COUNTIFS function applies criteria to cells across multiple ranges. The result is the calculation of how many times the criteria are met.
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
where
- criteria_range1. This is required. The first range in which to evaluate the associated criteria.
- criteria1 This also required. The criteria in the form of a number, expression, cell reference, or text that define which cells will be counted. The criteria can be expressed as 32, “>32”, B4, “apples”, or “32”.
- criteria_range2, criteria2, . These are optional. Additional ranges and their associated criteria. Up to 127 range/criteria pairs are allowed.
How Many Staff Are On Shift At Any Time of Day?
The COUNTIFS Formula used for this solution is below.
=COUNTIFS($C$6:$C$20,”<=”&G6,$D$6:$D$20,”>=”&G6)
This is really straightforward.
Cells C6:C20 contain the first criteria range. Take note of the Absolute cell referencing used. The first criteria is the time of shift being less than or equal to the working hour in cell G6.
Next, cells D6:D20 contain the second criteria range. The same absolute cell referencing is used. This time the second criteria is the shift being greater than or equal to the working hour in cell G6. There are 6 staff at 8 am, and at 10 am there are 12.
So, it is now possible to see how many staff on shift at any time during the day. Time for a cup of coffee! Have you used the COUNTIFS function before?.
So, if you want see see all of the blog posts in the #formulafriday series then you can click on this page right here.
Finally, if you want MORE Excel and VBA tips then sign up to my monthly newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips. You can read all of the blog posts in the Formula Friday Series by clicking on the link below.
How To Excel At Excel – Formula Friday Blog Posts.
If you want to also read Excel VBA tips, then feel free to join me every Monday for my Macro Monday series of blog posts. Click the link below to read all Macro Monday articles.