# LookUp Value and then Count From Specific Column If Not Blank

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.

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.

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?

• Please read how to and share a test sheet so as you can be easier helped. Feb 7, 2020 at 17:00

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

• 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