Relating text to a value
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 |
Relating text to a value
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" wrote in message ... 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 |
Relating text to a value
=COUNTIF(F12:L12,"D")*7.5+COUNTIF(F12:L12,"D/E")*15
|
Relating text to a value
Try
=COUNTIF(F12:L12,"X")*0+COUNTIF(F12:L12,"D")*7.5+C OUNTIF(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 |
Relating text to a value
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. |
Relating text to a value
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. |
All times are GMT +1. The time now is 08:35 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com