Results 1 to 2 of 2

Thread: How to use a Pivot Table to sum the last 12 columns
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Nov 2012
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default How to use a Pivot Table to sum the last 12 columns

    Hi Everyone,

    hope you can help....as usual.

    I have a pivot table based on an enormous database which essentially summarises each month points by individual.

    I need to keep all historical columns in the pivot (this is going back to 2012) but would like the pivot to calculate automatically the sum of the last 12 columns as soon as new data is added to it.

    I need the field to be inside the pivot because we need to sort from lowest to highest points. Adding a sum outside the pivot works but doesn't allow me to sort any value.

    Any way I can accomplish this?
    Many thanks for your help.

  2. #2
    Board Regular Worf's Avatar
    Join Date
    Oct 2011
    Location
    Rio, Brazil
    Posts
    3,742
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    2 Thread(s)

    Default Re: How to use a Pivot Table to sum the last 12 columns

    Hi

    The example below sums the last two columns:

    Order Report (2)

    ABCD
    3 Values
    4Row Labels Units TotalSum of Camp1
    5Andrews5925.474,196066,19
    6Binder158788,42946,42
    7Desk113.025,003036
    8Pen56111,44167,44
    9Pen Set2261.127,741353,74
    10Pencil141421,59562,59


    Excel tables to the web >> Excel Jeanie HTML 4
    Code:
    ' sheet module
    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Dim pf As PivotField, pfNew As PivotField, v, i%, c%, fs$
    Application.EnableEvents = False
    v = Split(Target.ColumnRange.Address, ":")
    c = Range(v(1)).Column
    fs = "="
    For i = c - 1 To c - 2 Step -1                      ' build formula
        fs = fs & Cells(Range(v(1)).Row, i) & "+"
    Next
    fs = Left(fs, Len(fs) - 1)                          ' final formula
    For Each pf In Target.CalculatedFields
        If pf.SourceName Like "*Camp*" Then             ' already exists
            pf.Delete
            Set pfNew = Target.CalculatedFields.Add("Camp1", fs)
        End If
    Next
    Target.AddDataField Target.PivotFields("Camp1"), "Sum of Camp1", xlSum
    Application.EnableEvents = True
    End Sub
    Excel 2013 / Windows 8.1 (home)
    Excel 2013 / windows 7 (work)


Some videos you may like

User Tag List

Tags for this Thread

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
  •