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