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

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.

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

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.