LinkBack URL
About LinkBacks
I have a SUM that sometimes needs to exclude some cells that are hidden.
How do I do this? There will still be data in the cell - I just dont want it in my = when those cells are hidden.
I have a SUM that sometimes needs to exclude some cells that are hidden.
How do I do this? There will still be data in the cell - I just dont want it in my = when those cells are hidden.
Try
=SUBTOTAL(109,A1:A100)
How are you hiding those cells, if it's via filtering or by hiding the row you can use SUBTOTAL function like this
=SUBTOTAL(109,A1:A100)
....or you can also try AGGREGATE function
=AGGREGATE(9,5,A1:A100)
Audere est facere
One option is to use a subtotal on those cells, as the subtotal of a hidden cell is zero.
So let's pretend you have data in Column C. Maybe out in column F you put =SUBTOTAL(9,C1) and then copy it down. Now SUM(F:F) and you will have the sum of the visible cells.
Make Mom proud: Add to my reputation if I helped out!
Make the Moderators happy: Mark the Thread as Solved if your question was answered!
I'm hiding them with a VBA (NOT like this example with groups)
I'm completly lost... could someone show me in this example how to do it?
Thanks guys!Mappe.xlsx
Well, if you're already using VBA you could just add a User Defined Function that sums only visible columns:
Please Login or Register to view this content.
Ah, those are in 'columns' not rows.
Subtotal won't work. I actually can't imagine any formula that would.
Is there anything common about which cells should be included?
You said you hide them with VBA, can you show that code?
What is the criteria that is used to hide/unhide a column?
That can likely be used in a sumif formula.
PERFECT!Originally Posted by daffodil11
Well, if you're already using VBA you could just add a User Defined Function that sums only visible columns:
Please Login or Register to view this content.
Thanks guys! - also the other suggestions!
It's a real kludge...
Using helper cells...
=CELL("width",A1)
Then...
=SUMIF(Helper Cells,">0",Sum Range)
However, hiding cells does not trigger a calculation. Maybe they can put in a calculate event after they hide the cells.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks