Forum Discussion
Converting text into numbers for sum on a roster
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.