First, define the following reference...

Insert Name Define

Name: Last5 (or any other name you prefer)

Refer to:

=LARGE(IF(SUBTOTAL(3,OFFSET(Cost,ROW(Cost)-MIN(ROW(Cost)),0,1)),ROW(Cost)
-MIN(ROW(Cost))),{1,2,3,4,5})

Click Ok

Then, use the following formula...

=SUMPRODUCT(SUBTOTAL(9,OFFSET(Cost,Last5,0,1)))

Hope this helps!

In article ,
"Sam via OfficeKB.com" wrote:

Hi All,

I previously received assistance from Domenic with the great working Formula
below BUT
can the Formula below be adapted to return the results of Filtered Visible
Cells?

=SUM(N(OFFSET(A1,LARGE(IF(Cost<"",ROW(Cost)),{1,2 ,3,4,5})-ROW(INDEX(Cost,1)),
0)))
...confirmed with CONTROL+SHIFT+ENTER.

The above Formula Sums the LAST 5 numeric values in a single column Dynamic
named Range - "Cost" .
The column also contains valid zero’s and invalid blanks (empty cells).

In Define Name Refers To box "Cost" defined as...
=Stock!$R$71:INDEX(Stock!$R$71:$R$65536,MATCH(9.99 999999999999E+307,Stock
!$R$71:$R$65536))

Assistance most appreciated.

Link to previous Thread:
http://www.officekb.com/Uwe/Forum.as...p-to-adapt-For
mula-syntax-to-work-with-Dynamic-Named#2faa1ecd66b74b338cdc9c754a29c239%40Offi
ceKB.com


Thanks Sam