1

We need to know how many times an employee has had a specific absence type for each AM and PM shift.
We have 2 worksheets.
The first, called Daily, has a list of employees (col A) with the shift time they were been absent (AM in col E and PM in col F) and the type for that absence (col G). Employees may be listed more than once, as each row is for a date, which is in another column and is irrelevant to the formula.

enter image description here

The second worksheet we are preparing, called Count, lists all company employees (col A) and the total number of Type CO and AN absences.
We need a formula in column G to find the employee name on the Daily worksheet (col A) and count the number of AM shifts (col E, not blank) where the Type is CO or AN (from col G). Then the same for the PM shifts in column H.

enter image description here

I would figure this to be some combination of a VLookUp and CountA, but I have not been able to get anything to work. Any Suggestions?

Improve this question
1

1 Answer 1

Reset to default
0

Using =query() you can count how many times there's a value in E:E and F:F

=QUERY(
    A:F,"
    SELECT A, count(E),count(F) 
    group by A
")

Test sheet

enter image description here

Improve this answer
2
  • I believe you are describing a sort of pivot table, using formulas. My only issue is that this doesn’t look at the Type column on the Daily worksheet for only types AN and CO, and it doesn’t account for staff that are listed on the Count worksheet and are not on the Daily sheet (to show 0 absences). I’ll play with this to see if I can find anything that may get me closer to the solution. Thank you for your input.
    – Kris
    Feb 9, 2020 at 12:43
  • No problem, you can add a new condition in the query for G to matches AN or CO =QUERY( A:G," SELECT A, count(E),count(F) WHERE G matches 'AN|CO' group by A ")
    – pjmg
    Feb 9, 2020 at 13:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.