Tricky one

[LondonJames]

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 th
"last n rows" (say n is 3 for this example) where the value in A is NO
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/matc
combinations. Don't want to use VBA

Many thanks!
Jame

 

[plinius]

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

Many thanks!
James

Put in cell I1 the value escluded (3)

In C4 this matricial formula:
=SUM((OFFSET(A4,,,-MATCH($I$1,COUNTIF(OFFSET(A4,,,-ROW(INDIRECT("$A$1:A"&ROW())),),"<>"&$I$1)),)<>$I$1)*OFFSET(A4,,1,-MATCH($I$1,COUNTIF(OFFSET(A4,,,-ROW(INDIRECT("$A$1:A"&ROW())),),"<>"&$I$1)),))
and fill down.

Bye,
E.