Relating text to a value

G

Guest

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
 
P

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
 
G

Guest

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.
 
B

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.
 
G

Guest

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top