Get values from last 3 non-blank cells in a row

Question

I currently have an excel worksheet similar to the following (#'s represent blank cells)

1   2   3   4   5   6   7
37  21  30  32  25  22  34
#   17  26  28  27  17  31
#   #   #   #   #   #   38
25  23  27  35  33  #   #
27  11  23  #   #   #   #

In column 8 I need the sum of the last 3 non-blank cells (the number of columns is increased regularly).

An example of what I need to achieve is below:

1   2   3   4   5   6   7       8
37  21  30  32  25  22  34      25+22+34=81
#   17  26  28  27  17  31      27+17+31=75
#   #   #   #   #   #   38      N/A
25  23  27  35  33  #   #       27+35+33=95
27  11  23  #   #   #   #       27+11+23=61

I have managed to get very close with the LARGE function but obviously this only give me the 3 largest, not the last 3 non-blank:

=(LARGE(C3:J3,1)+LARGE(C3:J3,2)+LARGE(C3:J3,3))

Solution 1

Here's another one:

=IF(COUNT(A2:G2)<3,"NA",SUM(G2:INDEX(A2:G2,LARGE(COLUMN(A2:G2)*(A2:G2<>""),3))))

From http://dailydoseofexcel.com/archives/2004/05/03/sum-last-three-golf-scores/

It identifies the column to sum from and since blank cells are zero, it doesn't matter how many are intermingled.

Solution 2

If data is in A1:G1 try this formula in H1

=IF(COUNT(A1:G1)>2,SUM(OFFSET(A1,0,MATCH(9.99E+307,A1:G1)-1,1,-3)),NA())

Solution 3

I know the pros here will hate this one. But I work with data in the hundreds of thousands of rows and millions of cells, And I prefer helper rows, and complex formulas over Array formulas any day. As it will cut calculations to fractions. Even if maintenance is more work, I spend less time fixing a formula once then waiting on calculations of arrays on every change of data (for me my data changes every 10 minutes) So, hundreds of thousands of arrays on millions of cells every 10 minutes will simply not work. So, here is just an alternative.

=IF(COUNT(A2:G2)<3,"NA",IF(COUNT(E2:G2)=3,SUM(E2:G2),IF(COUNT(D2:G2)=3,SUM(D2:G2),
 IF(COUNT(C2:G2)=3,SUM(C2:G2),IF(COUNT(B2:G2),SUM(B2:G2),IF(COUNT(A2:G2)=3,
 SUM(A2:G2)))))))

If there's not 3 values to sum it will return NA, other wise working right to left will check for the first cell with from that cell to the end has 3 number in it then will sum that.

If you data does involve more columns then your question. You can use dynamic ranges to clean up the code a little.

Another working non-array formula:

=SUM(IF(COUNT(A2:G2)=3,A2:G2,IF(COUNT(B2:G2)=3,B2:G2,IF(COUNT(C2:G2)=3,C2:G2,
IF(COUNT(D2:G2)=3,D2:G2,IF(COUNT(E2:G2)=3,E2:G2,"NA"))))))

simply put will sum what ever range from the end has a count of 3.

Solution 4

Newbee alert: I know the post is old however if anyones still out there, I'm keen to get help on derivative of the formula Dick provided whereby I'd like to run the count over several rows for example [2 in this case]:

=IF(COUNT(A1:G2)<3,"NA",SUM(G2:INDEX(A1:G2,LARGE(COLUMN(A1:G2)*(A1:G2<>""),3))))

Ultimately I'm trying to pull a sum of the last three cells with figures in running 12 accross

[A:L=months of year] by several rows down [1:5=consecutive years].

If anyone can shed light I'd be much obliged.

Many thanks: Ryan

View more solutions

Comments

[Siddharth Rout]

+ 1 Well, I'll be damned! LOL. I also had a formula ready but the rpoblem was that it didn't work if there were two same numbers...

[lethalMango]

This seems to work in most cases, however with the following in A1:G1 I get 58 rather than 88 (30 # 25 # 33 # #) - again, # is blank

[barry houdini]

I assumed from your examples that there wouldn't be blanks in amongst the the numbers, try Dick's version....

[lethalMango]

Sorry barry, I hadn't noticed my example all contained numbers in blocks. Thank you very much for your help though.

[Dick Kusleika]

Oh yeah, Ctrl+Shift+Enter may be important :) Thanks Barry