Using UDF to Sum only Visible Cells

[Jerry Sullivan]

I'm trying to implement Chip Pearson's isVisible UDF to Sum only visible cells.
http://www.cpearson.com/excel/IsVisible.aspx

I'm finding that the Array formula recalculates automatically when Rows are Hidden and Unhidden.

However the Array formula does not recalculate when Columns are Hidden or Unhidden.

Excel Workbook
	A	B	C	D	E	F	G	H
1	21		1	2	3	4	5	6
Before Hiding D-F

Excel Workbook
	A	B	C	G	H
1	21		1	5	6
After Hiding D-F

In fact, even if I click Calculate Now (F9), the value does not recalculate.
The formula recalculates if I change one of the inuput variables change, or if Application.Volatile is added to the UDF the formula recalculates on any change to the worksheet.

Strangely, Formula Auditing > Evaluate Formula shows this analysis:
SUM(isVisible(C1:H1)*C1:H1)
SUM({TRUE,FALSE,FALSE,FALSE,TRUE,TRUE}*C1:H1)
SUM({1,0,0,0,5,6})
21

Does anyone know why this is happening and a possible fix?

Note that my Columns are Hidden (not Filtered) so SubTotal will not work.

Thanks in advance!

 

[Biz]

Hi M8,

Try UDF below non array answer

Function Sum_Visible_Cells(Cells_To_Sum As Object)
       Dim cell As Range
       Dim Total As Double
       Total = 0
       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

Cell A1 =Sum_Visible_Cells(C1:H1)

Biz

 

[RoryA]

Hiding rows causes a recalc (because subtotal needs it to), hiding columns does not. Why not have the udf calc the sum?

 

[Jerry Sullivan]

Hiding rows causes a recalc (because subtotal needs it to), hiding columns does not. Why not have the udf calc the sum?

Ahhaaa... That explains it why. Rorya and Biz, thank you both for your suggestions.

I need to apply the same concept to some lease payment calculations, so I was hoping to use the more general-purpose isVisible UDF instead of bulding the payment calculations into the UDF. (I figured I'd start simple with a SUM function and work my way up...not expecting to hit this wall).

I had the same problem with Biz' UDF not recalculating upon Columns being Hidden/Unhidden. I might try adding a benign change to one of the dependent cells to see if that triggers a recalc.

 

[Biz]

Ahhaaa... That explains it why. Rorya and Biz, thank you both for your suggestions.

I need to apply the same concept to some lease payment calculations, so I was hoping to use the more general-purpose isVisible UDF instead of bulding the payment calculations into the UDF. (I figured I'd start simple with a SUM function and work my way up...not expecting to hit this wall).

I had the same problem with Biz' UDF not recalculating upon Columns being Hidden/Unhidden. I might try adding a benign change to one of the dependent cells to see if that triggers a recalc.

Press F9 to force UDF to calculate.

Biz

 

[Jerry Sullivan]

Press F9 to force UDF to calculate.

Biz

Well, that works...but it's a less-than-ideal work around.
I tried F9 with the isVisible UDF and that worked too. (not sure why it didn't earlier).

Thanks for your help Mate!

 

[Jerry Sullivan]

Since I'm hiding columns for various customers using VBA,
I will probably use the UDF (either isVisible or one that builds in the Interest Payments),
and add Range.Calculate to trigger a calculation of any cells with the UDF.

Sub HideColumns()
    Range("D:E").EntireColumn.Hidden = True
    Range("A1").Calculate
End Sub

Thanks again Biz and Rorya for pointing me in the right direction.