Summing non contiguous range minus last 2 cells of range?

[Simon Lloyd]

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

[Bob Phillips]

Array formula =SUM(A1:A20)-INDEX(A1:A20,MAX(IF(A1:A20"",ROW(A1:A20))))-INDEX(A1:A20,MAX(IF(A1:A20"",ROW(A1:A20)))-2)

[Simon Lloyd]

thanks for the reply Bob..and nice to hear from you!, i tried your suggestion which when pasted as is (i just changed the range!) it gives an error so i added a "," within the IF statements after the range: used to look like A1:A20"" now like A1:A20,"" but of course that showed #VALUE? then i used A1:A20="" or <>"" both gave #N/A?

Have i missed something?

Regards,
Simon

[Simon Lloyd]

Ok....guess it was a little my fault!, had a small typo in my range, i added <>"" and it now gives me a figure but it's the total minus the last figure not minus the last 2

Regards,
Simon

[Bob Phillips]

Should be minus the last two, there are tow minus functions in there. I am assuming data like Week 1 100 Week 2 150 Week 3 77 etc.

[Bob Phillips]

Of course, if my stupid accoun t would work you would see all that data on separate lines. I despair!

[Simon Lloyd]

Data is as follows Bob...reply by E-mail if you wish but don't sweat it as its only for home...nothing blindingly important!

(there is no space between text and number, can be if it would be better)
A1 Week1
A2 ?200
A3
A'n'
A2'n' Week2
A2'n ?150

etc. not in regular patterns, i have attached a sample

Regards,
Simon