+ Reply to Thread
Results 1 to 5 of 5

Running total on filtered table

  1. #1
    Registered User
    Join Date
    08-12-2019
    Location
    Australia
    MS-Off Ver
    Current
    Posts
    9

    Running total on filtered table

    Hi all,

    Struggling here with getting a formula to suit, hopefully someone can point me in the right direction.

    I have a table that generates 2 charts from calculated values on each line. The problem I have is that I need to filter this data and have the formulated cells update based on only visible data. I have read many things online and watched a whole bunch of vids but can't find the solution myself.


    So what I need is a formula for the running total $$ column and the running total units columns in the attachment. I need this to update as a cumulative sum of the filtered data.


    If anyone can help me here it would be greatly appreciated. I can feel the grey hairs starting to sprout every time i try something!


    Kind regards,
    Jim
    Attached Files Attached Files
    Last edited by Notooorious; 07-27-2020 at 03:27 AM.

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Running total on filtered table

    you might try SUBTOTAL, e.g.

    D10: =SUBTOTAL(9,B$10:B10)
    can be copied to E10

    as you filter these values will adapt accordingly

    depending on what you're doing with the subsequent charts, you might also want to test the below variant

    D10: =IF(SUBTOTAL(2,B10),SUBTOTAL(9,B$10:B10),NA())
    can be copied to E10

  3. #3
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Running total on filtered table

    with a pivot table.

    see the attached file.

    this result can also be done for the units.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  4. #4
    Registered User
    Join Date
    08-12-2019
    Location
    Australia
    MS-Off Ver
    Current
    Posts
    9

    Re: Running total on filtered table

    thanks for the tip, i have tried subtotal but it doesnt seem to be calculating correctly through the list, and also doubles the first value.

    Im not sure what im doing wrong here
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    08-12-2019
    Location
    Australia
    MS-Off Ver
    Current
    Posts
    9

    Re: Running total on filtered table

    Actually I worked out what I did wrong, i had a , instead of a : in the formula. thats got it sorted for me, thanks so much for your help. big headache gone for me!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Function to calculate a total on filtered table
    By WouterM in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-01-2017, 05:35 AM
  2. [SOLVED] Sum running total excluding filtered data
    By chewytoy in forum Excel General
    Replies: 2
    Last Post: 02-19-2015, 03:03 PM
  3. [SOLVED] How to do a running total of different accounts in a table
    By flapface in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 11-16-2014, 02:13 PM
  4. Replies: 4
    Last Post: 06-12-2013, 12:51 PM
  5. Grand total in Pivot Table to include filtered records
    By poorcloud in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 01-09-2013, 02:23 AM
  6. Changing the total with a filtered table
    By krie2509 in forum Excel General
    Replies: 2
    Last Post: 05-07-2010, 02:53 PM
  7. [SOLVED] % of Running Total to Grand Total in Pivot Table
    By David in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-17-2005, 04:05 PM

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