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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,303
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,884
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,884
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,209
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
 

Forum statistics

Threads
1,141,153
Messages
5,704,607
Members
421,359
Latest member
Edwardvanschothorst

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top