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


"SociologyStudent" <[email protected]> wrote in
message news:[email protected]...
> 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
>
>
>

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

"SociologyStudent" wrote:

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

[Brian Taylor]

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

[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!

"Brian Taylor" wrote:

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