• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

Andy LaPlante

New Member
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!
 

Attachments

  • SUM last 5 non-text non-blank values.xlsx
    16.5 KB · Views: 22
@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?
 
@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)
 
Back
Top