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