Probably Simple

[Simon2017]

Hi

Could really do with some assistance on this one ...svp

Table below . . . to achieve 16000 in A3 I've calc'd as follows; =$B3*((D3*E3)+(F3*G3)+(H3*I3)+(J3*K3)+(L3*M3)) ... this works but is 'long-hand' and doesn't address the rate changes in columns B & C.

Does anyone know a way of using 'sumproduct' or similar to show me how to calculate this using a better formula?

atb
Simon.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]wk1[/TD]
[TD][/TD]
[TD]wk2[/TD]
[TD][/TD]
[TD]wk3[/TD]
[TD][/TD]
[TD]wk4[/TD]
[TD][/TD]
[TD]wk5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]total cost[/TD]
[TD]day rate[/TD]
[TD]wend rate[/TD]
[TD]Qty[/TD]
[TD]Days[/TD]
[TD]Qty[/TD]
[TD]Days[/TD]
[TD]Qty[/TD]
[TD]Days[/TD]
[TD]Qty[/TD]
[TD]Days[/TD]
[TD]Qty[/TD]
[TD]Days[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]16000[/TD]
[TD]100[/TD]
[TD]250[/TD]
[TD][/TD]
[TD][/TD]
[TD]8[/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4000[/TD]
[TD]100[/TD]
[TD]250[/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2000[/TD]
[TD]100[/TD]
[TD]200[/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Maybe...

=SUMPRODUCT(D3:L3,E3:M3,--(E$2:M$2="Days"))*B3

M.

 

[Simon2017]

Thanks for that, M . . . it works a treat but I don't fully understand how . . . something to do with Offset?

Can the formula be adapted to allow for applying the Wend rate [column C] for when the Days exceed 5? ...ie; 6(Saturday) and 7(Sunday)

S.

 

[Marcelo Branco]

Can the formula be adapted to allow for applying the Wend rate [column C] for when the Days exceed 5? ...ie; 6(Saturday) and 7(Sunday)

S.

Don't understand what you asking for. Could you show examples?
I have to leave now - i'll take a look later.

M.

 

[Simon2017]

The numbers of 'Days' showing [in columns E,G,I,K,M] are typically equal to 5 ... Monday to Friday, these attract a Weekday Rate of £100 [col B]. If the numbers in cols E,G,I,K,M cells increase to 6, this means that Saturday is now to be included in the formula and, as a weekend rate, will attract the £250 . . . same for the scenario where cols E,G,I,K,M cells increase to 7, this means that Sunday is now to be included in the formula and, as a weekend rate also, will attract the £250 . . . Monday to Friday = upto 5 x £100, Monday to Saturday = 6 Days [5 x £100 and 1 x £250], Monday to Sunday = 7 Days [5 x £100 and 2 x £250]

Hope this is clearer, sorry for confusion.

S.

 

[Marcelo Branco]

Could you provide an example along with expected result where columns are greater than 5?

M.

 

[Simon2017]

Sure ... apologies for basic example, I'm not permitted to attach ... yet

S.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]WK1[/TD]
[TD][/TD]
[TD]WK2[/TD]
[TD][/TD]
[TD]WK3[/TD]
[TD][/TD]
[TD]WK4[/TD]
[TD][/TD]
[TD]WK5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]total cost[/TD]
[TD]day rate[/TD]
[TD]weekend rate[/TD]
[TD]Qty[/TD]
[TD]Days[/TD]
[TD]Qty[/TD]
[TD]Days[/TD]
[TD]Qty[/TD]
[TD]Days[/TD]
[TD]Qty[/TD]
[TD]Days[/TD]
[TD]Qty[/TD]
[TD]Days[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]16000[/TD]
[TD]100[/TD]
[TD]250[/TD]
[TD][/TD]
[TD][/TD]
[TD]8[/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]16750[/TD]
[TD]100[/TD]
[TD]250[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]8[/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]17000[/TD]
[TD]100[/TD]
[TD]250[/TD]
[TD]1[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]9000[/TD]
[TD]100[/TD]
[TD]250[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]4[/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Maybe...

A3 copied down
=SUMPRODUCT(D3:L3,E3:M3,--(E$2:M$2="Days"))*B3+SUMPRODUCT(D3:L3,E3:M3-5,--(E$2:M$2="DAYS"),--(E3:M3>5))*(C3-B3)

M.

 

[Simon2017]

Nice one . . . thanks for your time.

S.