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?

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 wrote:

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

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)

"Dave Peterson" wrote:

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 wrote:

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

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

On Feb 25, 10:05*pm, Julie wrote:
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)



"Dave Peterson" wrote:
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 wrote:

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- Hide quoted text -

- Show quoted text -

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

"Pete_UK" wrote:

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

On Feb 25, 10:05 pm, Julie wrote:
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)



"Dave Peterson" wrote:
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 wrote:

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- Hide quoted text -

- Show quoted text -

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

Pete

On Feb 25, 11:32*pm, Julie wrote:
How could I be so silly to have missed that. *Thank you both so much for your
help.
Greatly Appreciated.



"Pete_UK" wrote:
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

On Feb 25, 10:05 pm, Julie wrote:
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)

"Dave Peterson" wrote:
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 wrote:

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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -