Need to know a formula to count number of duties.


Currently, there are only 2 shifts of duties and 2 Employees. Employers are free to join at any time to the duty, cox shop is opened 24hrs. But their Salary will be made based on the number of duties they attended. Need to know a formula to count the number of duties attended by each employee.

It is also highlighted and shown in the Sheet also



OMG, Thank you sir a lot. I can't believe how much work load I'm about to release. This will save me a lot of time to do other works. Once again thank you. I really very much appreciate your help.
Nasr (rep: 4) Feb 2, '21 at 3:24 am

Happy to help. Thanks for selecting my Answer. 

Please note that the Answer from Variatus is quite similar but helpfully also shows you how to make fairly complex equations more easily readable/ [LINK URL="link_goes_here"]Visible_Text[/LINK]understandable. Suggest you read that too.

For future responses to Answers, kindly use the Discussion under the Answer rather that this (under the Question). 
John_Ru (rep: 1072) Feb 2, '21 at 4:03 am
Add to Discussion


Selected Answer

Hello Nasr

You can use COUNTIFS to do that but there are two versions of the formula needed. Both are in the attached revision to your file and detailed below.

This function counts each time a row in a range matches ALL criteria set. Each criteria test is a pair "Criteria Range, Criteria". In this case the first match needed is a row that matches the person's name so $C$5:$C$10 is the (fixed) range where the names appear against sign-in times and B17 is the name to match. In cell D17  the first pair is $C$5:$C$10,$B17 where row 17 in (fixed) column Bwith the name you want to match (and similar for others in B18:B20).

For the Day shift start formula, the other criteria are that the start time (in $D$5:$D$10) is greater than or equal to the shift start time in H5 but less than that for the night shift (in H6). In D17, those criteria are written is a special way (shown in bold below):


. D17 can be copied down to D19 (for the second employee etc.) since the $B17 bits will change.

It's slightly different for the night shift. You need to count the matches where the time is after 18:00 and those before 05:59. That's done in D18 by adding two COUNTIFS together, like this:


In the file, those formulae are in different shaded cells.

Hope this fixes your problem.



Thank you alot
Nasr (rep: 4) Feb 3, '21 at 12:10 am
Add to Discussion

Enter the formula below in cell D17 of your example and copy down.

=COUNTIFS(INDEX(SignIn,,1),$B17,INDEX(SignIn,,2),">="&IF($C17="day",0.25,0),INDEX(SignIn,,2),"<"&IF($C17="day",0.75,0.25)) + IF( $C17="night",COUNTIFS(INDEX(SignIn,,1),$B17,INDEX(SignIn,,2),">=0.75",INDEX(SignIn,,2),"<=1"),0)

The formula refers to the named range "SignIn" which is C5:D10 in your example, INDEX(SignIn,,1) specfies the range's first column, INDEX(SignIn,,2) specifies the second. These expressions can be replaced in the formula with $C$5:$C$10 and $D$5:$D$10 respectively. I think that the named range makes the formula easier to read but that may be a matter of taste. So, suit yourself. Bear in mind, however, that a named range can be made dynamic, meaning it can adjust automatically when the user adds or deletes rows. To achieve that behaviour without a named range takes a lot of functions which would render the above formula unmanagable.

To quickly assign a name to a range, type the desired name in the Name Bar, and press Enter. The name bar is the field above column A, to the left of the Formula Bar, where you normally see the address of the active cell.. After a range is named that name will appear in the Name Bar when selected. The Name Bar also has a drop-down list in which all named ranges on the tab are listed, and you can select one by clicking the name. Use the Name Manager, assible from the Ribbon's Formulas tab to modify an existing named range.



@ Variatus. Your solution is an  enhancement on my approach and gives a good learning point for the user. I've recommended it to the user (under Question). 
John_Ru (rep: 1072) Feb 2, '21 at 4:09 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login