Posted to microsoft.public.excel.worksheet.functions
|
|
Sum Visible Cells
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 -
|