Forum Discussion
Converting text into numbers for sum on a roster
You already have a perfectly acceptable Index Table. In 365 you could use
= SUM( XLOOKUP(Shift, ShiftHours[Code], ShiftHours[Value], 0) )as and alternative to HansVogelaar's approach. In other versions you could sort the table alphabetically by its first column and use LOOKUP
= SUMPRODUCT( IFERROR(LOOKUP(Shift, ShiftHours),0) )The TRANSPOSE idea also works fine.
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,
Thanks. I must have attached the download rather than the new version!
Thanks!
Hi. I have implemented both Hans's formula and mine on your newly-provided workbook. The error you obtained was caused by the fact that your reference table retained the default naming 'Table1' whereas Hans had assumed it to be renamed to 'ShiftHours'. Both solutions refer to a single (relative) column of the main data array. Hans used a direct referencing which needs to be re-specified; I used a Name 'shift' that needs to be defined within Name Manager.
I also introduced a new formula to generated the day numbers from the start date. It may be possible to generate all the totals as a single dynamic range using MMULT to sum the columns of data but I have yet to try that.
