Converting text into numbers for sum on a roster

New Contributor

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

@booragal 

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.

@Hans Vogelaar , 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.

@booragal 

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.

@booragal 

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 @Hans Vogelaar'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 @Peter Bartholomew ,

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,

 

 

@booragal 

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.

@booragal 

See the attached version.

 

@Peter Bartholomew : I don't see any formulas in your version...

@Hans Vogelaar 

Thanks.  I must have attached the download rather than the new version!

@booragal 

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)
  )

image.png

Have fun.