+ Reply to Thread
Results 1 to 4 of 4

GETPIVOTDATA - add multiple items in one item field

  1. #1
    Registered User
    Join Date
    05-09-2022
    Location
    Denmark
    MS-Off Ver
    Microsoft 365
    Posts
    2

    GETPIVOTDATA - add multiple items in one item field

    Hi everyone,

    I would like some help to add multiple items in one item field. Specifically I need to add multiple months in my formula below. Right now I'm only able to add one month (in this example April), but I would like to be able to add May, June, July and so on.

    =SUM(GETPIVOTDATA("[Measures].[SalesInvoicedTotal_DeliveredQty_ConsumerUnit_Ltr]";'Pivot table'!$A$10;"[Time].[Calendar Year]";"[Time].[Calendar Year].[All].[2022]";"[Products].[Item Group]";"[Products].[Item Group].[All].[Product1]";"[Time].[Month]";"[Time].[Month].[All].[April]"))

    I don't know if it could be done with an array. If so I'm not sure how to.

  2. #2
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    7,230

    Re: GETPIVOTDATA - add multiple items in one item field

    Yes, you should be able to use something like:

    =SUM(IFERROR(GETPIVOTDATA("[Measures].[SalesInvoicedTotal_DeliveredQty_ConsumerUnit_Ltr]";'Pivot table'!$A$10;"[Time].[Calendar Year]";"[Time].[Calendar Year].[All].[2022]";"[Products].[Item Group]";"[Products].[Item Group].[All].[Product1]";"[Time].[Month]";"[Time].[Month].[All].["&{"April","May","Jun"}&"]");0))

    You may need to use a different array separator based on your regional settings - the comma works for me.
    Rory

  3. #3
    Forum Contributor
    Join Date
    10-14-2020
    Location
    Shanghai
    MS-Off Ver
    O365
    Posts
    152

    Re: GETPIVOTDATA - add multiple items in one item field

    Hi

    use the bracket and a sum in front:
    Please Login or Register  to view this content.
    good luck

  4. #4
    Registered User
    Join Date
    05-09-2022
    Location
    Denmark
    MS-Off Ver
    Microsoft 365
    Posts
    2

    Re: GETPIVOTDATA - add multiple items in one item field

    Thank you Rorya.
    I tried with an array, but couldn't manage to do it the right way. But your solution works perfectly.

    Thanks again.

+ 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. Replies: 1
    Last Post: 12-28-2019, 06:41 AM
  2. Replies: 0
    Last Post: 08-15-2016, 02:07 PM
  3. [SOLVED] How do I sum up multiple items for the same field using cell references in GETPIVOTDATA?
    By vanbasten007 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-03-2014, 03:47 PM
  4. need to parse out a field with multiple items in field
    By rde55cae in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 07-25-2014, 10:28 PM
  5. GETPIVOTDATA all or none items in a field
    By tompope84 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-23-2013, 12:38 PM
  6. Replies: 2
    Last Post: 03-06-2009, 11:49 AM
  7. getpivotdata having multiple items
    By RoboStat in forum Excel General
    Replies: 1
    Last Post: 05-09-2006, 10:20 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