COUNTIF does not make any difference with regards to case so the reason you
got 52.5 (I don't get 60 if I change the D/N in K12 to E/N ) is that you
used both e/N and E/N
=SUMPRODUCT(COUNTIF(F12:L12,{"X";"D";"D/E";"E/N"}),{0;7.5;15;15})
will return 37.5
--
Regards,
Peo Sjoblom
Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey
"SociologyStudent" wrote in
message ...
Received the following formula suggestion:
=SUMPRODUCT(COUNTIF(F12:L12,{"X";"D";"D/E";"E/N";"e/N"}),{0;7.5;15;15;15})
to sum the following:
F G H I J K L
12 X X D D X D/E D
X = 0.0
D = 7.5
D/E = 15.0
Using the above formula returns the correct answer of 37.5.
However, if I change D/E to E/N (which = 15), the formula returns the sum
of
60.
I need the formula to recognize the following shift indicators:
D 7 - 3 7.5
E 3 - 11 7.5
N 11 - 7 7.5
d 7 - 3 7.5
e 3 - 11 7.5
n 11 - 7 7.5
ALD 7 - 3 7.5
ea 3-11 7.5
A combination of any 2 of these in one cell indicates a double shift
(D/E=15)
If I have D, E, E/N, N, each in their own cell, I need the formula to
return
37.5. If I change the E/N to N, I need the formula to return 30.00. I need
to
be able to change the shift indicator in any cell. I am calculating the
number of hours scheduled per week.