Addition problem (Excel 2007)

[steveh]

I am using a formula that Hans supplied me long ago to add upper case and lower case letters

In this example

=SUMPRODUCT(--EXACT(N7:AR7,"M"))+SUMPRODUCT(--EXACT(N7:AR7,"m"))/2

it correctly calculates that I have put in 5 x M =5 and 4 x m = 2 total = 7

But in this example

=SUMPRODUCT(--EXACT(N7:AR7,"L"))+SUMPRODUCT(--EXACT(N7:AR7,"l"))/2

It does not add the lower case l, I have entered 4 x L = 4 and 5 x l = 2.5 but the total shows 4

I do in fact have 13 conditions of upper and lower case altogether and it works in 11 of them but not for the above example or

=SUMPRODUCT(--EXACT(N7:AQ7,"P"))+SUMPRODUCT(--EXACT(N7:AQ7,"p"))/2

Everything looks to me to be the same?

[HansV]

Make sure that the cells really contain "l" (without the quotes), and not for example "l " or " l".

[steveh]

Make sure that the cells really contain "l" (without the quotes), and not for example "l " or " l".

Hi Hans

What is strange is that the cells are validated with a named range (leavetype) into which I typed

H
h
S
s
B
b

Etc.

And this was working OK but following your response I have gone back to the named range and retyped the whole lot in again and the l and p now work fine so thanks for your advice

Cheers