Using COUNTIFS to Determine Personnel Absence

[orientalist]

Hi All. Thank you for taking the time to read this post.

I am attempting to use the COUNTIFS function to determine the number of working days that employees are working each month. However, the function does not return a valid number. There are three worksheets.

The first includes the working days for the month.

Screen Shot 2017-01-03 at 03.48.03.png

The second includes data exported from a door card system (which employees swipe both when leaving and entering the premises).

Screen Shot 2017-01-03 at 03.48.28.png

The third worksheet attempts to count the number of days on which employees have made at least one contact with the door card system.

Screen Shot 2017-01-03 at 03.48.50.png

It would be really helpful if any one has a moment to look at this and let me know what I am doing wrong here.

Many thanks in advance.

[JBeaucaire]

This would work:

=SUM(INDEX((Attendance!$A$1:$A$13=$A2)*(MATCH(Attendance!$C$1:$C$13,Attendance!$C$1:$C$13,0)=ROW(Attendance!$C$1:$C$13)),0))


You can expand the range of rows, but don't try to apply to an entire column, just want you need.

[chullan88]

Hi JBeaucaire,

I guess the formula is not doing a check for working days.

[orientalist]

This would work:

=SUM(INDEX((Attendance!$A$1:$A$13=$A2)*(MATCH(Attendance!$C$1:$C$13,Attendance!$C$1:$C$13,0)=ROW(Attendance!$C$1:$C$13)),0))


You can expand the range of rows, but don't try to apply to an entire column, just want you need.

Thank you JBeaucaire! This enables me to count the number of days worked which is a sufficient short-term fix for me. I think chullan88 may be correct that this doesn't count the number of working days but that doesn't matter as much as being able to count the total number of days.

Apologies to ask for further help but I have tried to expand the formula to a new employee, Employee 3. However, when I expand the formula to reach the new row it shows as a 0. If you have any thoughts on this, I would be grateful. Thanks.

[Phuocam]

Try this ...

=SUM(--ISNUMBER(MATCH(WorkingDays!$A$2:$A$22,IF(A2=Attendance!$A$2:$A$14,Attendance!$C$2:$C$14),0)))

Enter with Ctrl+Shift+Enter.

[orientalist]

Try this ...

=SUM(--ISNUMBER(MATCH(WorkingDays!$A$2:$A$22,IF(A2=Attendance!$A$2:$A$14,Attendance!$C$2:$C$14),0)))

Enter with Ctrl+Shift+Enter.

Hi Phuocam. Thank you. This seems to be working. I'm grateful to you, JBeaucaire and chullan88 for your valuable help.

[orientalist]

[Deleted as duplicate post]

[chullan88]

Try this ...

=SUM(--ISNUMBER(MATCH(WorkingDays!$A$2:$A$22,IF(A2=Attendance!$A$2:$A$14,Attendance!$C$2:$C$14),0)))

Enter with Ctrl+Shift+Enter.

Hi Phoucam,
That's a cool formula!
Would you mind explaining it?