Share via

Cumulative total in pivot table?

Anonymous
2014-11-07T09:57:30+00:00

Hi - I have a dataset where each row relates to a work order which has a work initiated date, completed date and so on. There are over 50 thousand rows from over 8 years of data.

I’ve created a pivot table to establish the counts per month, of how many work orders still open by checking the content of the completed date. And also to find out the average number of days to complete in that month. So far so good.

Next I’d like to find out what is the cumulative number of open work orders at each month. So my thoughts are - at the start of each month it needs to know what is the total number of open work orders to that month and add to it the open number for that month itself. Can anyone please share how this can be done?

Thanks in advance.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

27 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2014-12-04T23:19:06+00:00

    Hi,

    This can be done with the Pivot Table of Excel 2013 - that has a DISTINCTCOUNT feature.  Here is a screenshot.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. HansV 462.6K Reputation points
    2014-11-08T12:33:51+00:00

    I added an extra column named Open to the source data, with formula =--(I2<>"CLOSED")

    I extended the source data of the pivot table to include the Open column, then added Open to the Values area, and set it to "Running total in" as described in my first reply.

    See https://www.dropbox.com/s/bgsgutm19p8ross/PivotWithCumulative.xlsx?dl=1

    Was this answer helpful?

    0 comments
  3. Anonymous
    2014-11-08T10:17:04+00:00

    Thanks - I've uploaded a very small dataset to OneDrive, please see:

    http://1drv.ms/1pzJS8H

    I need to find out by month, what is the cumulative number of open work orders at each month. From TableA the field Status has the content CLOSED or DO or PLAN. Anything that is not CLOSED is open.

    PivotA is the Pivot Table that I created from TableA, hope this makes sense and really appreciate your assistance.

    Was this answer helpful?

    0 comments
  4. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2014-11-07T23:31:14+00:00

    Hi,

    Upload the workbook to OneDrive and share the link of the workbook here.

    Was this answer helpful?

    0 comments
  5. HansV 462.6K Reputation points
    2014-11-07T13:26:13+00:00

    Add the same field that you used to count the work orders per month to the Values area again.

    Click on the dropdown arrow in this field in the Field List and select Value Field Settings from the dropdown menu., or right-click on any of the field's values in the pivot table itself and select Value Field Settings from the context menu.

    Select 'Running Total in' from the 'Show values as' dropdown, then select the relevant date field in the 'Base items' list.

    Click OK.

    Was this answer helpful?

    0 comments