Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sum only visible cells (excel 2000)

    Hello all,

    I have the need to some only visible cells in a column, but the column is not filtered using auto-filter so the subtotal(9, range) function does not work. )the row is manually hidden) I would like to do this without a macro if possible.

    Thanks

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sum only visible cells (excel 2000)

    If the rows are not hidden via autofilter, then Subtotal will not work. You can manually calculate the sum by selecting the range and then use Edit | GoTo | Special | Visible Cells Only, And then copying and pasting the data elsewhere, and finally summing it. John Walkenbach has a custom Worksheet function here to do what you want, but a UDF is probably not waht you are looking for.. Sorry for not giving a better solution. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sum only visible cells (excel 2000)

    I fear you'l need some VBA, for example a custom function - see XL2000: How to Programmatically Sum Only the Visible Cells in a Range.

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sum only visible cells (excel 2000)

    I've been rethinking your problem. I believe that I would redesign the sheet and add an extra column, place x's in that column in all of the rows that you want to hide. Then you could use autofilter & subtotal. This seems a much easier solution and it allows you to hide/unhide the rows easily. I always seem to forget which rows I want to hide. Plus, you can hide the extra column that you have added. You can even hide the auto-filter header. See attached example. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sum only visible cells (excel 2000)

    I like that - thinking out of the box! <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

  6. #6
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum only visible cells (excel 2000)

    Sammy,

    I used to do that when the data was small, as the company has grown, so has the data. The people that import this information will not go through 20,000 rows and put x's beside all the ones that are supposed to be hidden. I was afraid that the only feasable way to do this was to use a macro and have requested the programmers to add the one provided. They use one anyway to figure out which data to hide so I have asked them write it into the program. I just hate asking them to do stuff, they always complain.

    Thanks for all the help.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sum only visible cells (excel 2000)

    > They use one anyway to figure out which data to hide

    If there are criteria that determine which rows are to be hidden, those criteria might be used in a formula (without using code). But since they use code anyway, adding the calculation will be a cinch.

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum only visible cells (excel 2000)

    <hr>I just hate asking them to do stuff, they always complain.<hr>
    Next time, ask me, I never complain! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Sum only visible cells (excel 2000)

    <hr>Next time, ask me, I never complain!<hr>
    You may not do it publicly, but what about privately? <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    Steve

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum only visible cells (excel 2000)

    <hr>You may not do it publicly, but what about privately? <hr>
    I swear, I never even do that privately <img src=/S/innocent.gif border=0 alt=innocent width=20 height=20>
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •