total / count visible cells.

[useless]

I'm stuck. I can't figure out how to make this work.

I want to total the cell values / count number of the visible cell
that fall inside cell value parameters. i.e. 4 < 10.

As far as the funciton is concerned the table has two heading
destination and weight. I want to used autofilter to hide unwante
destinations but leave all weights to that destination visible.

I started with an example from Microsoft.


Function Sum_Visible_Cells(Cells_To_Sum As Object)
Application.Volatile
For Each cell In Cells_To_Sum
If cell.Rows.Hidden = False Then
If cell.Columns.Hidden = False Then
total = total + cell.Value
End If
End If
Next
Sum_Visible_Cells = total
End Function

I hope what I want to achieve makes sense. Any help is greatl
appreciated

 

[Frank Kabel]

Hi
a formula solution:
1. Sum
=SUMPRODUCT(--(SUBTOTAL(3,OFFSET($A$1,ROW($A$1:$A$10)-ROW(I$A$1),0))=1)
,--($A$1:$A$10=">4"),--($A$1:$A$10="<10"),$A$1:$A$10)

2. Count:
=SUMPRODUCT(--(SUBTOTAL(3,OFFSET($A$1,ROW($A$1:$A$10)-ROW(I$A$1),0))=1)
,--($A$1:$A$10=">4"),--($A$1:$A$10="<10"))