Case sensitive sumproduct

[Bering]

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:

[TABLE="width: 172"]
<tbody>[TR]
[TD]Leave Name[/TD]
[TD]Code[/TD]
[/TR]
[TR]
[TD]Sick Leave[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]Holiday[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]Parental Leave[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]Training[/TD]
[TD]T[/TD]
[/TR]
[TR]
[TD]Half Day[/TD]
[TD]h[/TD]
[/TR]
[TR]
[TD]Recuperation[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD]Business trip[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]Extraordinary[/TD]
[TD]X[/TD]
[/TR]
</tbody>[/TABLE]


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)

 

[Eric W]

Try:

=SUMPRODUCT((OFFSET($A6,0,1,1,372)<>"")*(OFFSET($A$3,0,1,1,372))*EXACT(OFFSET($A6,0,1,1,372),NP$5))

 

[Bering]

Try:

=SUMPRODUCT((OFFSET($A6,0,1,1,372)<>"")*(OFFSET($A$3,0,1,1,372))*EXACT((OFFSET($A6,0,1,1,372),NP$5))

Thank you so much, it works perfectly.

 

[Eric W]

Glad to help.