Tricky one
 

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

Tricky one
 

"LondonJames" ha scritto nel messaggio
...

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




--
LondonJames

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.