+ Reply to Thread
Results 1 to 2 of 2

How to keep a running sum of a column that changes when I sort it?

  1. #1
    Registered User
    Join Date
    12-28-2018
    Location
    Pennsylvania, US
    MS-Off Ver
    Excel 2016-Windows 10
    Posts
    24

    How to keep a running sum of a column that changes when I sort it?

    I want to have a column summing the total of the percentages next to it. So for example, I have revenue in my first 3 rows of 10, 5, and 1. They make up 10%, 5%, and 1% of revenue and I show that in my 2nd column. I want my 3rd column to show 10%, 15%, and 16% to represent the sum of the percentages to that row's point. If the table wasn't going to be re-sorted and altered I could easily do that by just taking the sum above the cell and adding the % of rev to it.

    However, when I re-sort my data, now one of the cells that was referencing the sum above it now has a header above it and is trying to add up a header and gets a value error causing everything after it to have a value error. Also, the previous first row in the column that just took the value of the column next to it still does that halfway down the sheet, completely resetting any sum calculated up until that point.

    How can I sum up the % of revenue column as I go along without having that problem when I sort the data a different way? I have attached an excel file with a small, crude example of my problem.
    Attached Files Attached Files

  2. #2
    Forum Expert Sambo kid's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office Professionsl Plus 2016- work; 2012 for Mac at home
    Posts
    5,555

    Re: How to keep a running sum of a column that changes when I sort it?

    If I'm following your example, I just applied a filter (on the data tab of excel) across rows S5 through Y5 then sorted in S5 values descending and it kept your values and matched those in A through G (mostly).
    only differences were in your col D vs col X and col G vs col Y but the totals still come out to 100% in each by the time you get to item 4 (or 1) in row 9. It differs due to the formula you have there taking the 0 and adding it to the values in row 6, then the value in X7 and adding it to the value in Y6 etc. Again, they all come out to 100% by the bottom.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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