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.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1				wk1		wk2		wk3		wk4		wk5
2	total cost	day rate	wend rate	Qty	Days	Qty	Days	Qty	Days	Qty	Days	Qty	Days
3	16000	100	250			8	5	8	5	8	5	8	5
4	4000	100	250			2	5	2	5	2	5	2	5
5	2000	100	200			2	5	2	5		5		5
6
7
8

<tbody>
</tbody>

 

[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.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1				WK1		WK2		WK3		WK4		WK5
2	total cost	day rate	weekend rate	Qty	Days	Qty	Days	Qty	Days	Qty	Days	Qty	Days
3	16000	100	250			8	5	8	5	8	5	8	5
4	16750	100	250	1	6	8	5	8	5	8	5	8	5
5	17000	100	250	1	7	8	5	8	5	8	5	8	5
6	9000	100	250	4	5	4	6	4	7

<tbody>
</tbody>

 

[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.