Thread: Sum last n rows based on condition (SUMIF won't cut it)

Hi all,

I have the following example set of data

n|A|B
1|1|10
2|2|55
3|2|34
4|1|12
5|3|45
6|2|344
7|3|32
8|3|56
9|2|23

Each row needs to have a sum formula in row C, which needs to sum the "last n rows" (say n is 3 for this example) where the value in A is NOT 3

So:
In row 3, it would sum and return rows 1 2 3.
In row 4, it would sum and return rows 2 3 4
In row 6, it would sum and return rows 6 4 3 (skipping row 5)
In row 9, it would sum and return rows 9 6 5 (skipping rows 8 and 7)

I cant seem to work this out, even using fancy sumproduct/match combinations. Don't want to use VBA

SUMIF won't do the trick unless we can feed a dynamic range into it.

Spreadsheet attached as requested

Many thanks!
James

In row 3 enter the formula:

=IF(A3=3,"",INDEX($B$1:B3,LARGE(IF($A$1:A3=3,0,ROW($A$1:A3)),1),1)+INDEX($B$1:B3,LARGE(IF($A$1:A3=3, 0,ROW($A$1:A3)),2),1)+INDEX($B$1:B3,LARGE(IF($A$1:A3=3,0,ROW($A$1:A3)),3),1))

And copy down.

This is an array formula and must be entered using Ctrl-Shift-Enter.

Thanks for your reply Andrew, unfortunately in my simplification of my spreadsheet and question I think I misguided you.

What I have is many rows of figures. Every Nth row (we can say 5 or 10, but this will be fixed) I want to output a total of the last N rows. It's similar to a paging scheme I guess.
However, some rows need to be ignored (where An="3" for example).

So if we have 30 rows of numbers, I want a summary total to appear on the 10th/20th/30th line, etc. However, if there's a "3" then that line needs to act as if it's not actually there. So the total would appear on the 11th row (or 12th row if there are 2 "3"s found.

I hope this makes more sense

Ah, right - assuming your data is in columns A and B (A containing the 1-3 values, B containing the numbers to be summed) and starts at row 1 then this formula can be copied down from the 2nd line onwards:

=IF(AND(MOD(ROW()-COUNTIF($A$1:A2,3),10)=0,A2<>3),SUMIF(OFFSET($A$1,MAX(INDEX(IF($C$1:C1<>"",ROW($A$1:A1),0),0)),0):A2,"<>3",OFFSET($B$1,MA X(INDEX(IF($C$1:C1<>"",ROW($A$1:A1)),0)),0):B2),"")

Again it is an array formula, so must be entered with Ctrl-Shift-Enter.

The bit in bold in the formula is the minimum number of rows gap between sub-totals.

Andrew, that works a treat, thanks! Would it be possible to modify your formula to now give a slightly different result?

Imagine for the rows with "3" (the ignored rows) have no total but just a blank cell.
Would it be possible to return the address/range for the "current page" that that row falls in?

Say,we have no "3" rows, then row 6 would return a1:a10. Row 8 would return a1:a10, but row 15 would return a11:a20.
Now with the "3" rows included, they'd still appear in the same range.

Is that a completely new formula or can yours be adjusted to do that?


Many thanks

This might be easiest with an example.

So, this is the file I was working with to get to the last formula. The first sub-total appears on row 17, with rows 1-16 being blank.

Are you now asking if we can leave the sub-totals as they are, but in cells C1:C16 have the value "A1-A17"?

Andrew, thanks again. I appreciate your time here!

So far your formulae are very useful and I'm trying to integrate them with mine, but I guess my spreadsheet is setup differently

I've attached a new spreadsheet with some expansion and comments, with the hope that it makes more sense.

Thanks again

Andrew, I've deconstructed your formulae and integrated it into mine. They work a treat, thanks for your help!

Hugely appreciated

Kind regards,
James

No problem at all, James.