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

Answer 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.

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