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

 

[Norie]

Sean

How are the cells being hidden?

If it's by a filter you could use SUBTOTAL.

=SUBTOTAL(9,C10:C90)

 

[seenfresh]

I don't have them hidden by Filters. They are just grouped into subcolumns by using SHIFT+ALT+arrow key

Thanks

Sean

 

[pgc01]

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

 

[DominicB]

Good evening seenfresh

To sum only visible cells, you can use the SubTotal function with a reference of 109, thus :

=SUBTOTAL(109,C10:C90)

HTH

DominicB

 

[pgc01]

Hi Sean

DominicB posted a perfect solution, (another one I've learned today).

Just a remark. If I'm not wrong, in the quick search I made, it seems that you need to have excel 2003. Hope you have it. If not, my UDF works in older versions, at least in my excel 2000.

Cheers
PGC

 

[DominicB]

Hi pgc01

Spot on! I only noticed it a few months ago and wondered why I'd never found it before.

And now I know why...

DominicB

 

[Aladin Akyurek]

Hi pgc01

Spot on! I only noticed it a few months ago and wondered why I'd never found it before.

And now I know why...

DominicB

That option is only available on versions after Excel 2000...

 

[seenfresh]

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

 

[DominicB]

Hi seenfresh

Are you using Excel 2000 or prior? If so you'll have to use the custom function pgc01 supplied.

HTH

DominicB