Hello,
I have a problem with a formula in a staff holiday tracker.
The tracker has the employees names in Coloumn A, from row 6 to row 15.
Coloumns B-NI correspond to the days of the year: each employee enters the leave codes, as per the table below:
<tbody>
</tbody>
In range NL6 - NS15 I have the below formula to a summarise sick leaves, holidays and so on by employee, the headers in range NL5 - NS5 are: S H P T h R B X
SUMPRODUCT((OFFSET($A6,0,1,1,372)<>"")*(OFFSET($A$3,0,1,1,372))*((OFFSET($A6,0,1,1,372))=NP$5))
My problem is that, since the formula does not distinguish H from h, the count is incorrect in column NP (half days).
Is there a way to amend it to be case sensitive?
Many thanks
PS: cell A3 contains the number corresponding to the month (1 for Jan, 2 for Feb and so on)
I have a problem with a formula in a staff holiday tracker.
The tracker has the employees names in Coloumn A, from row 6 to row 15.
Coloumns B-NI correspond to the days of the year: each employee enters the leave codes, as per the table below:
Leave Name | Code |
Sick Leave | S |
Holiday | H |
Parental Leave | P |
Training | T |
Half Day | h |
Recuperation | R |
Business trip | B |
Extraordinary | X |
<tbody>
</tbody>
In range NL6 - NS15 I have the below formula to a summarise sick leaves, holidays and so on by employee, the headers in range NL5 - NS5 are: S H P T h R B X
SUMPRODUCT((OFFSET($A6,0,1,1,372)<>"")*(OFFSET($A$3,0,1,1,372))*((OFFSET($A6,0,1,1,372))=NP$5))
My problem is that, since the formula does not distinguish H from h, the count is incorrect in column NP (half days).
Is there a way to amend it to be case sensitive?
Many thanks
PS: cell A3 contains the number corresponding to the month (1 for Jan, 2 for Feb and so on)
Last edited: