PDA

View Full Version : Summing non contiguous range minus last 2 cells of range?



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

Bob Phillips
03-25-2007, 11:17 AM
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
03-25-2007, 11:35 AM
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
03-25-2007, 11:45 AM
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
03-25-2007, 12:47 PM
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
03-25-2007, 12:47 PM
Of course, if my stupid accoun t would work you would see all that data on separate lines. I despair!

Simon Lloyd
03-25-2007, 01:04 PM
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