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

seenfresh

Well-known Member
Joined
Jul 12, 2006
Messages
1,141
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
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,911
Office Version
  1. 365
Platform
  1. Windows
Sean

How are the cells being hidden?

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

=SUBTOTAL(9,C10:C90)
 

seenfresh

Well-known Member
Joined
Jul 12, 2006
Messages
1,141
I don't have them hidden by Filters. They are just grouped into subcolumns by using SHIFT+ALT+arrow key

Thanks

Sean
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,870
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


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

Well-known Member
Joined
Oct 3, 2005
Messages
1,569

ADVERTISEMENT

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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,870
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

Well-known Member
Joined
Oct 3, 2005
Messages
1,569

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

Well-known Member
Joined
Jul 12, 2006
Messages
1,141
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

Well-known Member
Joined
Oct 3, 2005
Messages
1,569
Hi seenfresh

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

HTH

DominicB
 

Watch MrExcel Video

Forum statistics

Threads
1,111,910
Messages
5,541,536
Members
410,547
Latest member
htran4
Top