Help with formula that will sum number values within text in a cell range
-
-
-
uploaded example document
-
Hello,
Thanks for your test file ...:wink:
Attached is a proposal with Array Formulas
Hope this will help you
-
Once you have tested the Formulas ... feel free to share your comments ...
-
This is great and I think I understand the array formulas you have provided, but I am again stumped when applying them to calculate overtime when the entry also includes the shift Type (I.e., D3-8 OT). I need to be able to calculate the total hours for OT and also add the hours to the appropriate shift type column (D2 or D3). I've attached an example for you.
-
-
Hello,
Instead of adding different examples on each and every row ...with each new message ...
Why don't you list ONCE for all the entire scope of your possibilities ... by describing your generic input structure ...
Attached is Version 2 ... to solve the unique second example you have provided ...
-
That is the only other anomaly where overtime can be mixed with shift type. Everything else can be handled with the formulas you have already provided.
-
Have you tested Version 2 ...???
-
You're amazing! That solved my dilemma with the OT calculation, but how do I tally the hours for each shift type? In the example you provided I'm looking for the 8 hours in OT to also be added to the D3 column when calculating the shift type hours (as shown below). I do apologize for being difficult. [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 251"]
[tr]
[td]OT
[/td]
[td]CE
[/td]
[td]D2
[/td]
[td]D3
[/td]
[/tr]
[tr]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]16
[/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td][/td]
[td][/td]
[td]32
[/td]
[td]48
[/td]
[/tr]
[/TABLE] -
Just to ensure clear communication ... why don't you attach your test file ...with your expected results added manually ...
-
-
see thigh orange highlighted cells Columns D2 and D3
-
Attached is your Version 3
You should take a little bit of time to analyze the formulas ...
Hope this will help
-
Hope latest version provides all answers to your questions ...
-
This is perfect, thank you very kindly!:)
-
Thanks a lot for your Thanks ...AND for the Like ..:smile:
-
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!