Forum Discussion
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 12 hour night shift is C12, an 8 hour night shift is C8, an 8-hour morning shift is A8 etc.
I would like to add the combined hours in each column. However, as you can guess Excel won't recognise the codes as numbers when I hit the sum button.
I believe I may need to create an index table, but I can't find any easy instructions on how to create one, and then how to use it.
Thanks!!
Device and OS Platform: Windows 10 and Mac BigSur
Excel Version: Microsoft Excel for Mac, 16.47.1
10 Replies
Since you are a 365 user, I have added 3 further solutions. The first 'Arr' uses MMULT to evaluate the daily manning levels for the month as a single calculation. The second 'LET' uses the LET function to provide additional local variable to make the calculation more meaningful. The final one 'LAM' uses functionality that is still on beta release. The LAMBDA function will allow the user to define a formula in terms of dummy variables and later provide the actual variables as parameters, thus allowing the user to define their own functions.
Somewhere along the line, you will reach the point where your reaction is OMG, let me out of here! I still believe there is no harm in knowing that other solution strategies are possible.
Arr
= MMULT( SIGN(TRANSPOSE(fte)), XLOOKUP(ShiftData,ShiftHours[Code],ShiftHours[Value],0) )LET
= LET( rowFilter,SIGN(TRANSPOSE(fte)), shiftValues,XLOOKUP(ShiftData,ShiftHours[Code],ShiftHours[Value],0), MMULT(rowFilter,shiftValues) )LAM
= LET( shiftValues,XLOOKUP(ShiftData,ShiftHours[Code],ShiftHours[Value],0), FILTEREDTOTAL(shiftValues,SIGN(fte))) where FILTEREDTOTAL is defined to be = LAMBDA(Array,filter, MMULT(TRANSPOSE(filter),Array) )Have fun.
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,
As far as I can tell, the second total should be 0+12+0+8+0+12+12+8+0+8+8+0=68, not 60.
Let's say that the data for 17/5/21 are in B3:B14, and that the index table is named ShiftHours.
The first total is returned by the following array formula, confirmed with Ctrl+Shift+Enter:
=SUMPRODUCT(ShiftHours[Value]*(TRANSPOSE(B3:B14)=ShiftHours[Code]))This can be filled or copied to the right.
HansVogelaar , thank you so much for replying.
Yes, you're right the column should calculate 68 hours. My mistake, how embarrassing!
Can you point me in the right direction on how to create an index table?
(How do I create one, where do I contain, how do I access it etc etc)
Thank you again.
The index table is the table in your screenshot, the one with Code and Value as headers. You can place it anywhere. When you create the formula, simply point to the Value column and to the Code column (without the header); Excel will automatically enter the correct reference.
