Relating text to a value

[Guest]

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.

 

[Peo Sjoblom]

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