Is it poss. To Sum Visible Cells Only in a selected array?

[seenfresh]

Is it possible to just sum visible cells only within a desired column or selected array?

I have visible cells in Column C10:C90,

Any advice would be greatly appreciated,

Sean

 

[seenfresh]

Using Excel 2006,

Thanks

 

[riaz]

Hi Sean

I may be wrong but I don't think that you can just sum visible cells with a native excel formula.

However, if you need to do it, one alternative is to use an UDF and use it like a formula, like

=sumvisible(D3:D19)

I did an example, just paste the code in a regular module.

Hope this helps
PGC

Option Explicit

Function SumVisible(rR As Range) As Double
Dim rC As Range, dtotal As Double

For Each rC In rR
    If Not rC.EntireRow.Hidden And Not rC.EntireColumn.Hidden Then dtotal = dtotal + rC
Next
SumVisible = dtotal
End Function

Hello

This is exactly what I was looking for, thank you for it. One small problem I have is that when I put in the UDF, it totals the range correctly. If I then hide a cell in the range, I need to go the cell with the UDF in it, hit F2 and enter to "activate" it so the total excludes the hidden cell.

The reason I want to use this is that we have people who type in numbers in cells that are hidden later due to some criteria. The totals, however, look correct, but if you manually add up the visible printed cells, they don't add up to what is shown. This UDF would put the total out of sync, which would then be highlighted by a conditional format.

Any way of making this an automatic recalc if a cell is hidden/exposed?

Many thanks
Riaz

 

[Domenic]

Re: Thanks

I have entered in the subtotal function with a reference to 109 as such:

SUBTOTAL(109,$J$17:$J$85)= #VALUE

Did I miss something?

Thanks gentleman,

Sean

Does #VALUE! appear anywhere in J17:J85?