|
|
Without Blank Cells in the Data
The following formulas return the sum of the last 3 values from B2:B10,
where the data does not contain blank cells...
=SUM(INDEX(B2:B10,MATCH(9.99999999999999E+307,B2:B10)-3+1):B10)
or
=SUM(OFFSET(B2:B10,MATCH(9.99999999999999E+307,B2:B10)-3,0,3))
Based on the sample data, the formulas return 200. Sample
Workbook
With
Blank Cells in the Data
The
following formula returns the sum of the last 3
values from B2:B10, where the data contains blank cells...
=SUM(INDEX(B2:B10,LARGE(IF(B2:B10<>"",ROW(B2:B10)-ROW(B2)+1),3)):B10)
| Note
that this is an array formula, which needs
to be confirmed with
CONTROL+SHIFT+ENTER for the PC or COMMAND+RETURN for the Mac. |
Based on the sample data, the formula returns 225. Sample
Workbook |
|
|
