Conditional formatting based on sum of values with the same entries in another column

Hi,

I'm not sure how to even look for this exact solution, so apologies if this is was solved before, however, I need a conditional formatting based on a formula that would look for the same values in one column of a table (i.e. name of the employee) and then apply formatting to cells in the same rows but another column based on the sum of values in those cells, if it does not meet specified criteria.

The problem is I would like to be as simple as possible (so no VBA or queries) and work on Excel 2016 (so no filter / unique formulas) so the most users can actually make a use of it.
The names of employees are also subject to change, so I cannot make a formula for each known entry separately.

On the example of the attached spreadsheet:
The conditional formatting would highlight any cells in specific column (each day with separate formatting) if the sum of values in that column for "norm" entry in column F is less than 8 in all rows with the same name of the employee (column B).

E.g. 1. John Smith have 4 hours of normal time working with Team 1 and 3 hours of normal time working with Team 2 on the 3rd of January. I would like to have both those values highlighted as their sum is less than 8.
E.g. 2. Michael Johnson have 4 hours of normal time working with Team 1 and 4 hours of normal time working with Team 2 on the 3rd of January. Additionally he did 2 hours overtime ("OT") with Team 1 on that day. I want the "4"s to not be highlighted as he worked total of 8 hours normal time on that day, which is expected.

Any new name can be added in the B column in any number of rows in the table and they are not always sorted but the conditional formatting looks for any rows with the same employee name in the column B and sums up all values for specific day.

I hope I made the request clear enough.

Try

CF formula

=AND($F6="Norm",SUMPRODUCT((G$6:G$10)*(G$6:G$10<>0)*($B$6:$B$10=$B6)*($F$6:$F$10="Norm")*($G$5:$AK$5=G$5))>0,SUMPRODUCT((G$6:G$10)*(G$6:G$10<>0)*($B$6:$B$10=$B6)*($F$6:$F$10="Norm")*($G$5:$AK$5=G$5))<8)

Thank you for the feedback and best wishes for the New Year to you.

Hey John, one more request after all - would it be possible to make it work when values other than numbers are entered to the column?
It seems to stop working when I put a comment like "Absent" instead of number.

Try

=AND(SUMIFS(G$6:G$13,$B$6:$B$13,$B6,$F$6:$F$13,"Norm")>0,SUMIFS(G$6:G$13,$B$6:$B$13,$B6,$F$6:$F$13,"Norm")<8)

and ensure OT CF is top of the list (see attached)