Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Relating text to a value
Received the following formula suggestion: =SUMPRODUCT(COUNTIF(F12:L12,{"X";"D";"D/E";"E/N";"e/N"}),{0;7.5;15;15;15}) to sum the following: F G H I J K L 12 X X D D X D/E D X = 0.0 D = 7.5 D/E = 15.0 Using the above formula returns the correct answer of 37.5. However, if I change D/E to E/N (which = 15), the formula returns the sum of 60. I need the formula to recognize the following shift indicators: D 7 - 3 7.5 E 3 - 11 7.5 N 11 - 7 7.5 d 7 - 3 7.5 e 3 - 11 7.5 n 11 - 7 7.5 ALD 7 - 3 7.5 ea 3-11 7.5 A combination of any 2 of these in one cell indicates a double shift (D/E=15) If I have D, E, E/N, N, each in their own cell, I need the formula to return 37.5. If I change the E/N to N, I need the formula to return 30.00. I need to be able to change the shift indicator in any cell. I am calculating the number of hours scheduled per week. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Relating text to a value
COUNTIF does not make any difference with regards to case so the reason you
got 52.5 (I don't get 60 if I change the D/N in K12 to E/N ) is that you used both e/N and E/N =SUMPRODUCT(COUNTIF(F12:L12,{"X";"D";"D/E";"E/N"}),{0;7.5;15;15}) will return 37.5 -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law of holes; if you are in one stop digging." Lord Healey "SociologyStudent" wrote in message ... Received the following formula suggestion: =SUMPRODUCT(COUNTIF(F12:L12,{"X";"D";"D/E";"E/N";"e/N"}),{0;7.5;15;15;15}) to sum the following: F G H I J K L 12 X X D D X D/E D X = 0.0 D = 7.5 D/E = 15.0 Using the above formula returns the correct answer of 37.5. However, if I change D/E to E/N (which = 15), the formula returns the sum of 60. I need the formula to recognize the following shift indicators: D 7 - 3 7.5 E 3 - 11 7.5 N 11 - 7 7.5 d 7 - 3 7.5 e 3 - 11 7.5 n 11 - 7 7.5 ALD 7 - 3 7.5 ea 3-11 7.5 A combination of any 2 of these in one cell indicates a double shift (D/E=15) If I have D, E, E/N, N, each in their own cell, I need the formula to return 37.5. If I change the E/N to N, I need the formula to return 30.00. I need to be able to change the shift indicator in any cell. I am calculating the number of hours scheduled per week. |
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) |