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:

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)

 

[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.