Forum Discussion
Marcy123
Sep 22, 2025Copper Contributor
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
ITEM | SUM |
APPLES | 5.2 |
PEARS | 3.2 |
CHERRY | 0.4 |
TOAST | 0.2 |
ORANGE | 1.8 |
WATER | 0.4 |
LIME | 2.5 |
PIVOT#2
ITEM: | SUM |
PEARS | 1.8 |
BERRY | 0.7 |
PEACH | 1.4 |
KIWI | 1.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
- PeterBartholomew1Silver Contributor
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) )
- mathetesSilver 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.
- What are the sources for those two Pivot Tables? Are they separate databases, but all in the same workbook?
- Have you considered combining the raw data--those (presumably) separate databases--so that you're doing just a single Pivot Table summary?
- 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?
- In other words, are you open to different approaches to solving the question?
- 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... ?