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.
|