Forum Discussion

booragal's avatar
booragal
Copper Contributor
Apr 05, 2021

Converting text into numbers for sum on a roster

Hello, I am working on a roster that contains a number of different shifts that are for different hours. On the roster, we have different codes so people know what they are working. For example. A...
excel
Excel on Mac
Formulas and Functions
office 365
booragal's avatar
booragal
Copper Contributor
Apr 05, 2021

Hi PeterBartholomew1 ,

Thanks so much for trying to help.

I really feel like a goose because I can't grasp how to make the formulas work that both you and Hans are suggesting. I understand the concept.

When I pasted the formula into one of my columns in the cell next to total hours.

= SUM( XLOOKUP(Shift, ShiftHours[Code], ShiftHours[Value], 0) )

I received the error "There's a problem with formula ... not trying to type a formula? etc etc.

I suspect when I am pasting this formula I'm not linking it to anything, or I am missing a step to make this formula active.

 

I've attached a copy of the spreadsheet, and just replaced the names with generic ones.

If someone can show me how to put it together, I am very happy to learn how to do the rest.

We have Office 365.

 

Thank you again,

 

 

Resources