Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Summing visible cells only

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    129
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I want to sum just the cells I can see when I have a filter on. Do I need to have a column which contains a 1 when the the row is visible and a zero otherwise, and use SUMPRODUCT or is there an easier way? Thanks

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Have you tried using the SUBTOTAL worksheet function?

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    129
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yes, I don't want to use subtotals in this job. Is there another way?

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,026
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    Try to use the filter criteria directly in a SUMPRODUCT formula if you must.

  5. #5
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    Try this UDF...

    Function SumVisible(RefRange As Range)
    Dim UsedCell As Range, temp As Double
    Application.Volatile

    For Each UsedCell In RefRange
    If Not UsedCell.EntireRow.Hidden And Not UsedCell.EntireColumn.Hidden Then
    If IsNumeric(UsedCell.Value) Then
    temp = temp + UsedCell.Value
    End If
    End If
    Next UsedCell
    SumVisible = temp
    End Function

    Bye,
    Jay

    EDIT: Added Application.Volatile to make it more accurate, but hiding and unhiding rows and columns is not a trappable event, I believe, so be careful when (if) using this.

    [ This Message was edited by: Jay Petrulis on 2002-04-26 09:29 ]

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Tim,

    I'd go with Mark's =SUBTOTAL function suggestion, it's different from data / subtotals

    it will sum only visible cells when filtering is on - changing the filter criteria will result in a new sum....
    :: Pharma Z - Family drugstore ::

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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