+ Reply to Thread
Results 1 to 6 of 6

Calculating totals in a table

  1. #1
    Registered User
    Join Date
    04-11-2013
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    92

    Calculating totals in a table

    Hello,

    I have a table ranging from B9 to I9 downwards several thousand rows long, with the number of rows changing each day.

    I have filters in my headers in order to filter and extract detailed information from the table.

    I want to somehow calculate the sum for column G9down and H9down, these sums should ignore the results from rows which have been filtered out, and be displayed on, lets say, cells k6 and K7.

    Thanks in advance

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,405

    Re: Calculating totals in a table

    Use SUBTOTAL function

    I.E. =SUBTOTAL(109, G9:G10000)

    where 109 is for filtered SUM.

  3. #3
    Registered User
    Join Date
    04-11-2013
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    92

    Re: Calculating totals in a table

    What do you mean by filtered sum?

  4. #4
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Calculating totals in a table

    The function numbers for the subtotal function are:
    Function_num(includes hidden values) Function_num(ignores hidden values) Function
    1 101 AVERAGE
    2 102 COUNT
    3 103 COUNTA
    4 104 MAX
    5 105 MIN
    6 106 PRODUCT
    7 107 STDEV
    8 108 STDEVP
    9 109 SUM
    10 110 VAR
    11 111 VARP
    Where 109 would be a sum ignoring hidden values
    Last edited by ragulduy; 06-04-2013 at 08:36 AM.

  5. #5
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,405

    Re: Calculating totals in a table

    Quote Originally Posted by jsuarez199 View Post
    What do you mean by filtered sum?
    When you filter your table this formula will show you sum of viewable cells (while SUM(G1:G10000) will give you total sum).

  6. #6
    Registered User
    Join Date
    04-11-2013
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    92

    Re: Calculating totals in a table

    Awesome thanks a lot zbor!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1