Forum Discussion

Marcy123's avatar
Marcy123
Copper Contributor
Sep 22, 2025

SUMIF help

Trying to create a total based on 2 pivot tables.  What formula do I put in the column in red text.

 

PIVOT #1

ITEMSUM
APPLES5.2
PEARS3.2
CHERRY0.4
TOAST0.2
ORANGE1.8
WATER0.4
LIME2.5

PIVOT#2

 

ITEM:SUM
PEARS1.8
BERRY0.7
PEACH1.4
KIWI1.4

WHAT FORMULA DO YOU PUT IN SO THAT IT WILL SEARCH MATCHING 1ST COLUMN AND ADD TOTALS FROM BOTH PIVOTS TO THIS 3RD LIST.

ITEM:TOTAL (PIVOT #1 AND #2) THAT MATCH ITEM 
APPLES 
PEARS 
CHERRY 
TOAST 
ORANGE 
WATER 
MUSHROOM 
KIWI 

2 Replies

  • To pick up from mathetes​ post, it is probably easiest not to use a Pivot Table in the first place.  If that is what you have you will need a list of distinct items then use GETPIVOTDATA to extract values from each.

    distinctItems
    = SORT(UNIQUE(VSTACK(Table1[Item], Table2[Item])))
    
    = IFERROR(GETPIVOTDATA("Amount", pivotTable1,"Item", distinctItems), 0)
    + IFERROR(GETPIVOTDATA("Amount", pivotTable2,"Item", distinctItems), 0)

    If, on the other hand, you have access to the source data then you could stack the data first then pivot it

    = LET(
        stackedItems,  VSTACK(Table1[Item], Table2[Item]),
        stackedAmount, VSTACK(Table1[Amount], Table2[Amount]),
        GROUPBY(stackedItems, stackedAmount, SUM)
      )

     

  • mathetes's avatar
    mathetes
    Silver Contributor

    Your question raises other questions. At this point, when you've had quite a few views but no response, I guess I'll venture into asking one or two of those questions. 

    1. What are the sources for those two Pivot Tables? Are they separate databases, but all in the same workbook?
    2. Have you considered combining the raw data--those (presumably) separate databases--so that you're doing just a single Pivot Table summary?
    3. Are you aware of some of the Dynamic Array functions that would enable you, in effect, to virtually combine two sets of data and then summarize from that new virtual array?
    4. In other words, are you open to different approaches to solving the question?
    5. Finally, is it possible for you to share the actual workbook (which I presume doesn't really involve types of foods) or a mockup that does illustrate the issue, but includes raw data that is disguised in the form of counts of food types... ?

Resources