Sum Visible Cells

[Julie]

I have a column with the following formula in every row associated with data
(ie. Q5:Q32):

=IF(AND(B25<=L25,L25<480),"2","1")

In the "Totals" cell (Q33), I have the following formula:

{=SUM(VALUE(Q5:Q32))}

But, I need the "Totals" cell to only sum the visible cells when filtering.
I have tried the following VB function:

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

But this does not work. Can anyone tell me how to fix this issue?

 

[Dave Peterson]

If you return real numbers in the first formula:
=IF(AND(B25<=L25,L25<480),2,1)

Then you could use
=subtotal(9,q5:q32)

And life would be easier--well, if you're not hiding columns. If you're hiding
columns, then this won't work.

 

[Julie]

The result of the first formula returns either number 2 or 1. How do I
ensure they are "real numbers"? All affected columns are unhidden but
"=subtotal(9,q5:q32)" still returns a value of 0 (zero)

 

[Pete_UK]

You are not returning number 2 or 1 - you are returning the text
values "2" or "1", so change your first formula to this:

=IF(AND(B25<=L25,L25<480),2,1)

as Dave had pointed out to you (i.e. get rid of the quotes around the
numbers).

Pete

 

[Julie]

How could I be so silly to have missed that. Thank you both so much for your
help.
Greatly Appreciated.

 

[Pete_UK]

You're welcome, Julie - thanks for feeding back.

Pete