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

#### seenfresh

##### Well-known Member
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

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sean

How are the cells being hidden?

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

=SUBTOTAL(9,C10:C90)

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

Thanks

Sean

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``````

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

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

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

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...

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

Hi seenfresh

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

HTH

DominicB

Replies
2
Views
417
Replies
5
Views
168
Replies
2
Views
128
Replies
1
Views
362
Replies
1
Views
159

1,219,892
Messages
6,150,800
Members
450,985
Latest member
Andynair7

### 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.

### Which adblocker are you using?

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

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