Simon Lloyd

03-25-2007, 09:24 AM

Hi all, i have a column that contains both text and figures like this

Week1

?200.00

i use this formula in another cell to sum the range =IF(COUNTIF(J3:J3000,"Week"&1),SUM(J3:J3000),""), the data in the range is non contiguous week2 may be 10 cells down week 3 may only be 2 cells down from that.

What i would like to do using a formula is sum the range minus the last two weeks, so if in the range i have week1 to week9 then i only want to sum week1 to week7.

The words Week'n' appear in one cell and the figures directly below that this is always the case. I can do this in VBA no problem but need a formula solution!

Regards,

Simon

Week1

?200.00

i use this formula in another cell to sum the range =IF(COUNTIF(J3:J3000,"Week"&1),SUM(J3:J3000),""), the data in the range is non contiguous week2 may be 10 cells down week 3 may only be 2 cells down from that.

What i would like to do using a formula is sum the range minus the last two weeks, so if in the range i have week1 to week9 then i only want to sum week1 to week7.

The words Week'n' appear in one cell and the figures directly below that this is always the case. I can do this in VBA no problem but need a formula solution!

Regards,

Simon