Sum Up the last 7 (n) numbers in a column ignoring the error

[Arnefer1206]

Good Day!

Hope anyone could assist me

I have this data in column A

A1 - 5
A2 - 10
A3 -15
A4 - 9
A5 - 5
A6 - 7
A7 - 20
A8 - #N/A
A9 - 14
A10 - 4

I want to get the sum of the last 7 cell value containing numbers and ignoring the cell with error (#N/A)

The result must be 74 - adding up (A10, A9, A7, A5, A4, A3)

 

[JoeMo]

If the error(s) is (are) always #N/A, this appears to work (A8 formula added just to generate a #N/A error):

Book1
	A	B	C
1	5		74
2	10
3	15
4	9
5	5
6	7
7	20
8	#N/A
9	14
10	4
Sheet1

Cell Formulas
Range	Formula
C1	C1	=AGGREGATE(9,6,OFFSET(A10,0,0,-7-COUNTIF(A1:A10,"#N/A")))
A8	A8	=MATCH(5,I1:I10,0)

 

[StephenCrump]

=IFERROR(AGGREGATE(9,6,INDEX(A:A,LARGE(IF(ISNUMBER(A1:A10),ROW(A1:A10)),7)):A10),"<7 numbers") array-entered

Haven't checked exhaustively, but looks as if it may be more robust, e.g. try #N/A in A2 instead of A8.

 

[Peter_SSs]

this appears to work

If you add another #N/A in, say, A1 then it fails.

Here is my suggestion, also avoiding the volatile function OFFSET.

Book1
	A	B	C
1	5		74
2	10
3	15
4	9
5	5
6	7
7	20
8	#N/A
9	14
10	4
Sum Last 7

Cell Formulas
Range	Formula
C1	C1	=AGGREGATE(9,6,INDEX(A:A,AGGREGATE(14,6,ROW(A1:A10)/ISNUMBER(A1:A10),7)):A10)
A8	A8	=NA()

 

[StephenCrump]

... ROW(A1:A10)/ISNUMBER(A1:A10) ..

Ah, nice!

No need for the clunkier IF(ISNUMBER(A1:A10),ROW(A1:A10)) when we can use AGGREGATE twice to ignore errors.

 

[Arnefer1206]

If the error(s) is (are) always #N/A, this appears to work (A8 formula added just to generate a #N/A error):

Book1
	A	B	C
1	5		74
2	10
3	15
4	9
5	5
6	7
7	20
8	#N/A
9	14
10	4
Sheet1

Cell Formulas
Range	Formula
C1	C1	=AGGREGATE(9,6,OFFSET(A10,0,0,-7-COUNTIF(A1:A10,"#N/A")))
A8	A8	=MATCH(5,I1:I10,0)

Hi Joe

Thanks for your effort, i tried to replace other value in column A such as A8 change to #N/A and, yes it does work, for that example only
However whenever I applied it on my data it seems there is some error and the result is not the same as I expected

 

[Peter_SSs]

You had 2 other suggestions to try. Did you get the same results with those?

However whenever I applied it on my data it seems there is some error and the result is not the same as I expected

Perhaps you could share a bit more about your data and what result you got v what result you expected?
If we cannot replicate what you have, it's pretty hard to guess what might be wrong.

 

[Arnefer1206]

=IFERROR(AGGREGATE(9,6,INDEX(A:A,LARGE(IF(ISNUMBER(A1:A10),ROW(A1:A10)),7)):A10),"<7 numbers") array-entered

Haven't checked exhaustively, but looks as if it may be more robust, e.g. try #N/A in A2 instead of A8.

Hi Stephen

This is so great, a very robust function, I finally accomplished my worksheet with a reliable result.
I'm dealing with the interpretation of the Control Chart with algorithms in the Spread Sheet. The error I encountered is when my series of data has gap between each other, then with this formula it has been solved.

I hope I could further understand the nature of the functions in excel like you.

 

[Arnefer1206]

You had 2 other suggestions to try. Did you get the same results with those?

Perhaps you could share a bit more about your data and what result you got v what result you expected?
If we cannot replicate what you have, it's pretty hard to guess what might be wrong.

Sir could your teach me how to upload an excel .xlam file here, same as in your comment, so I could share some of my data

 

[Arnefer1206]

=IFERROR(AGGREGATE(9,6,INDEX(A:A,LARGE(IF(ISNUMBER(A1:A10),ROW(A1:A10)),7)):A10),"<7 numbers") array-entered

Haven't checked exhaustively, but looks as if it may be more robust, e.g. try #N/A in A2 instead of A8.

Sir Stephen

Could your spare me more

Its almost same with my sample data

A1 - Increased
A2 - Increased
A3 - Decreased
A4 - Decreased
A5 - Maintain
A6 - Increased
A7 - Increased
A8 - Increased
A9 - #N/A
A10 - #N/A
A11 - #N/A
A12 - Decreased
A13 - Decreased
A14 - #N/A
A15 - #N/A

I want to get the count of the last 8 cell value containing "Increased" or "Decreased" and ignoring the cell with error (#N/A)

The result must be

"Increased" - 3
"Decreased" - 4

The 7 cells to be counted are {A13, A12, A8, A7, A6, A5, A4, and A3} , ignoring the the cells {A15, A14, A11, A10 and A9} because it contains error (#N/A)