mitch_e_los
New Member
- Joined
- Mar 29, 2017
- Messages
- 2
Hi,
I'm looking for some help to derive a solution that I cannot solve myself - this has been driving me insane as I feel I'm so close but I keep returning a #value error in the array.
I would like to calculate the total cost of labour based on the sum of hours booked in a week, multiplied by a lookup (or index/match) for the grade of a staff member for multiple staff members, in a single cell.
in the example below, I would like to achieve :
The sum of hours for grade A ((7+14+12+10)+(2+1+3+4)), multiplied by the lookup (or match/index) for Grade A's rate (£40)+ the sum of hours for grade B (2+0+5+7), multipled by the lookup (or match/index) for Grade B's rate (£50) and so on...
<tbody>
</tbody>
<tbody>
</tbody>
<tbody>
</tbody>
Now I could just add a column to the first table, lets call it column F and do the vlookup, then use CSE and sum(F2:F5*B2:E5) - this gives me the right answer. However I cannot add another column - this the key limitation - so the answer is to achieve it in one calculation.
So I think I want
But I cannot make the Vlookup work as an Array. It does'nt work either with an equivalent index/match combo.
Can any one help solve how to compute this figure in one calculation please, without VBA?
Kind regards
Mitch_e_los
I'm looking for some help to derive a solution that I cannot solve myself - this has been driving me insane as I feel I'm so close but I keep returning a #value error in the array.
I would like to calculate the total cost of labour based on the sum of hours booked in a week, multiplied by a lookup (or index/match) for the grade of a staff member for multiple staff members, in a single cell.
in the example below, I would like to achieve :
The sum of hours for grade A ((7+14+12+10)+(2+1+3+4)), multiplied by the lookup (or match/index) for Grade A's rate (£40)+ the sum of hours for grade B (2+0+5+7), multipled by the lookup (or match/index) for Grade B's rate (£50) and so on...
Grade | wk 1 | wk2 | wk 3 | wk4 |
A | 7 | 14 | 12 | 10 |
B | 2 | 0 | 5 | 7 |
C | 5 | 30 | 8 | 14 |
A | 2 | 1 | 3 | 4 |
<tbody>
</tbody>
Grade | Cost per hour |
A | £40 |
B | £50 |
C | £60 |
<tbody>
</tbody>
Total Cost | £???? |
<tbody>
</tbody>
Now I could just add a column to the first table, lets call it column F and do the vlookup, then use CSE and sum(F2:F5*B2:E5) - this gives me the right answer. However I cannot add another column - this the key limitation - so the answer is to achieve it in one calculation.
So I think I want
But I cannot make the Vlookup work as an Array. It does'nt work either with an equivalent index/match combo.
Can any one help solve how to compute this figure in one calculation please, without VBA?
Kind regards
Mitch_e_los