Sum based on Variable end text

[RoscoW88]

Morning,

I am looking for a formula that can extend based on a cell value.

I have a table from P1 > P12 and i need it to sum from P1 to value in U2 (P11)

I have attached a test sheet.

As always, any help is greatly appreciated!

Regards
Ross

[zbor]

Try this:

=SUM(OFFSET($B$1,1,1,,MATCH(LOOKUP($U1,$R$2:$R$25,Q$2:Q$25),$B$1:$M$1,0)-1))

[josephteh]

Try, =SUM(TAKE(B2:M2,,MATCH(U2,B1:M1,0)))

[josephteh]

Are your financial period end dates always the last Sundays of the month?

If yes, then you could apply this formula in cell S3 and copy down: =EOMONTH(S2,1)-WEEKDAY(EOMONTH(S2,1),2)+IF(WEEKDAY(EOMONTH(S2,1),2)=7,7,0)

[RoscoW88]

Try this:

=SUM(OFFSET($B$1,1,1,,MATCH(LOOKUP($U1,$R$2:$R$25,Q$2:Q$25),$B$1:$M$1,0)-1))

Cheers zbor - tried this but only ended up with 500 - not 550 as expected?

Try, =SUM(TAKE(B2:M2,,MATCH(U2,B1:M1,0)))

This works great thanks Joseph

Are your financial period end dates always the last Sundays of the month?

If yes, then you could apply this formula in cell S3 and copy down: =EOMONTH(S2,1)-WEEKDAY(EOMONTH(S2,1),2)+IF(WEEKDAY(EOMONTH(S2,1),2)=7,7,0)

Sort of, but works on a 5/4/4 basis.... you other answer works perfect so no worries on this one.


Thank you all for your help!!

[josephteh]

You are welcome and thanks for the Rep. You could use these formulas for Start Date and End Date:

End Date in $S$2: =R2+IF(MOD(MID(Q2,2,2),3)=1,5,4)*7-1
Start Date in $R$3: =S2+1

[TMS]

One way:

Please Login or Register  to view this content.