Relating text to a value

[SociologyStudent]

Can I relate text to a number so I can sum a string of text? I'm trying to
create a work schedule and ensure each employee is working 32.00 hours per
week.
Example:
F12 G12 H12 I12 J12 K12 L12 M12
Katrina Harris X X D D D/E D X (sum of F12:L12)

X = off or 0.00
D/E = 15
D = 7.5

M12 should equal 37.5

 

[Peo Sjoblom]

D+D+D is 45 + 7.5 is 52.5 not 37.5
to me it looks like the values in F12:L12 are

X, X, D, D, D/E, D, X

not so?

anyway this will work

=SUMPRODUCT(COUNTIF(F12:L12,{"X";"D";"D/E"}),{0;15;7.5})

--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com

 

[Brian Taylor]

=COUNTIF(F12:L12,"D")*7.5+COUNTIF(F12:L12,"D/E")*15

 

[Barb Reinhardt]

Try

=COUNTIF(F12:L12,"X")*0+COUNTIF(F12:L12,"D")*7.5+COUNTIF(F12:L12,"D/E")*15

The first part really isn't needed, but if you want to change the X to be
something besides 0, you can just change the 0 value.

 

[Brian Taylor]

Peo,

D = 7.5 and D/E = 15. I think you got it backwards. But I like your
formula. Pretty slick: a sumproduct, countif, and two arrays. You are
my hero.

 

[SociologyStudent]

You are correct Mr. Taylor. Thank you for clarifying for me. You all were
very helpful. Thanks!