Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Relating text to a value
=COUNTIF(F12:L12,"D")*7.5+COUNTIF(F12:L12,"D/E")*15
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
extra help with transpose | Excel Discussion (Misc queries) | |||
Shade cell according to text? | Excel Discussion (Misc queries) | |||
Using Concatenate function to generate text in Text Box | Charts and Charting in Excel | |||
SUMPRODUCT vs Text??? | Excel Worksheet Functions | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) |