Excel 2007 : Sumproduct Remove Case Sensitivity

[TommyG]

Hi all,

I have been using this formula,

=SUMPRODUCT(EXACT(G6:CR6,{"H";"HD"})*{1;0.5})

on an attendance sheet i use for work, it counts "H" as 1 and "HD" as 0.5 which is great.

However i want it to also recognise lowercase "h" and "hd", I've been trying a few things but nothing seems to work, any ideas appreciated.

[DonkeyOte]

EXACT "does what it says on the tin" - apple <> Apple

If no case sensitivity required then no need for SUMPRODUCT in first instance:

=SUM(COUNTIF(G6:CR6,{"h","hd"})*{1,0.5))

[TommyG]

Thanks very much just what i needed, just pointing out a slight error for anyone else with same problem.

=SUM(COUNTIF(G6:CR6,{"h","hd"})*{1,0.5})
not
=SUM(COUNTIF(G6:CR6,{"h","hd"})*{1,0.5))

[DonkeyOte]

Thanks for pointing out the typo