CODEWITHSUNDEEP
vbaexcelworksheet-function
lethalMango
lethalMango

Reputation: 4491

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

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

Upvotes: 10

Views: 15506

Answers (4)

Ryan
Ryan

Reputation: 11

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

Upvotes: 1

user2140261
user2140261

Reputation: 7993

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.

Upvotes: 2

Dick Kusleika
Dick Kusleika

Reputation: 33145

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.

Upvotes: 8

barry houdini
barry houdini

Reputation: 46341

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())

Upvotes: 3

Related Questions