I'm using Excel 2010 and need to try create a formula that will automatically calculate total hours. In the below example I would like to calculate the overtime hours (OT) in records D2:Q2 and place the sum of the OT numbers in T2 and the CE numbers in U2. In addition to that, I'd like to be able to calculate the total hours for each shift type (E.g., D2, D3) and place the sum in the appropriate V3 or W3. Any help you all could provide would be greatly appreciated.
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., D38 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!