Happy Friday and time for more #formulafun on #formulafriday and today we are going to look at a fun calculation that will show us how many people are working at one time in a retail store every hour, you could just as easily use this 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. We base this 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 use with this question.
First let get the scheduled working times of our staff which we can see below. We now need to see how many staff on are on shift at any given time.
Let’s take a quick look at the function we are going to use.
The COUNTIFS Syntax
The COUNTIFS function applies criteria to cells across multiple ranges and counts the number of times all criteria are met.
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
The COUNTIFS function syntax has the following arguments:
- 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. For example, criteria can be expressed as 32, “>32”, B4, “apples”, or “32”.
criteria_range2, criteria2, … 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?
our formula is
This is really straightforward.
Out first criteria range is contained in cells C6:C20, notice we use absolute cell referencing with the $ around the cells, so we can drag the formula down the column of data, and our first criteria is the time of shift being less than or equal to the the working hour in cell G6.
Our second criteria range is contained in cells D6:D20 ( with the same absolute cell referencing as our first criteria range), and this time the second criteria is the shift being greater than or equal to the working hour in cell G6. we then drag the formula down and can see that we have 6 staff at 8am, and at 10am we have 12 etc etc.
So now we can easily see how many staff on on shift at anytime during the day. Time for a cup of coffeee!
That’s it job done and easily calculated by Excel.
If you want see see all of the blog posts in the #formulafriday series then you can click on this page right here.
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. If you want to see all of the blog posts in the Formula Friday Series you can do so by clicking on the link below.