SUM last 5 non-zero non-text values in a row

[Andy LaPlante]

Hi all! First time poster but have been a silent following of the forums for several years. Great service this site provides many people so thank you very much. Anyway, I'm trying to sum the last 5 values in a given row, but only those that do not contain text or that aren't blank (or have a zero). I was able to pick up a similar function on this forum for the non-blank (or non-zero) values, but am stuck with how to incorporate a function to ignore the text values too. In my worksheet, which I've attached, row 2, columns A through K are the following values:
38.1, 29.5, 43.4, 7.8, 17.7, 15.3, 0, 36.2, 26.7, B, 28

How do I write a formula that sums the last 5 values in row 2 but that ignores the "0" in G2 and the "B" in J2 ?

Currently I'm using the following formula that ignores the blanks/zeros but not sure what to add to this to ignore the "B" entries. I'm doing this same thing for about 100 rows, each of which contains one cell with that "B" value.

=SUMPRODUCT((COLUMN(A2:K2)>=LARGE(COLUMN(A2:K2)*(A2:K2>0),5))*(A2:K2))

Thanks in advance!

 

[NARAYANK991]

Hi ,

Try the following array formula , to be entered using CTRL SHIFT ENTER :

=SUM((COLUMN(A2:K2)>=LARGE(COLUMN(A2:K2)*(A2:K2>0)*NOT(ISTEXT(A2:K2)),5))*IF(ISTEXT(A2:K2),0,(A2:K2)))

Narayan

 

[Andy LaPlante]

It worked!!!! Thank you very much Narayan. Very much appreciate your help.

 

[Lori]

Nice solution. Fwiw, i think the above formula could be shortened a little:

=SUM((COLUMN(A2:K2)>=LARGE(COLUMN(A2:K2)*(N(+A2:K2)>0),5))*N(+A2:K2))

 

[NARAYANK991]

Nice solution. Fwiw, i think the above formula could be shortened a little:

=SUM((COLUMN(A2:K2)>=LARGE(COLUMN(A2:K2)*(N(+A2:K2)>0),5))*N(+A2:K2))

Now I remember your earlier use of + !

Thanks.

Narayan

 

[Asheesh]

@Lori or @NARAYANK991

You guys are Awsome J

Just want to check If I understand it right

Isnt this “+” converting the reference range into an array...?

Is there any other use of it?

 

[Lori]

@Asheesh: indeed, there seems to be a common myth that unary plus doesn't do anything, and i have yet to see it used elsewhere in forums. The function of the plus sign is essentially to convert a range to an array but it does have a number of uses where the effect is not necessarily immediately obvious, eg:

http://superuser.com/a/935645/122736
http://stackoverflow.com/a/32539642/1252820

Another application is for looking up values across sheets:
=MATCH(TRUE,CELL("contents",IF(1,+INDIRECT("Sheet"&{1,2,3}&"!A1"))),0)

 

[Asheesh]

Thank you @Lori - Interesting...