How to Summarise Positive / Negative Number without Adding New Column in Excel Pivot Table

Question

Raw data is following format:

Name        Team    Talent
---------------------------
Quill        Red       500
Drax         Red      -900
Ego         Blue     2,000
Kraglin      Red      -200
Rocket       Red       900

How can we make a pivot table like below without adding new column to original data:

Team     Sum_Pos_Talent   Sum_Neg_Talent
-----------------------------------------
Red               1,400           -1,100
Blue              2,000                0

Tried adding calculated fields, but the calculation is on the number on pivot table, not on each row of data.

score 1 · Answer 1 · answered May 12 '17 at 07:12

1

You can use SUMIFS.

Sum_Pos_Talent

=SUMIFS($C$2:$C$6,$B$2:$B$6,E2,$C$2:$C$6,">0")

Where C2:C6 is the Talent column, B2:B6 is the Team Column, E2 is the name of the team in the other table.

Similarly Sum_Neg_Talent

=SUMIFS($C$2:$C$6,$B$2:$B$6,E2,$C$2:$C$6,"<0")

answered May 12 '17 at 07:12

nightcrawler23

2,056
1
14
22

Thanks. But this is part of a larger pivot table. So want to make all calculations within that pivot table. – Alpha May 12 '17 at 07:50

score 0 · Answer 2 · edited May 23 '17 at 12:02

0

Sort the data to separate positives from negatives and insert a new row of headers between the two sets (say rename Talent to Sum_Pos_Talent and for inserted header switch to Sum_Neg_Talent). Then, assuming Team is in B1 (and in B5) create the PT from multiple consolidation ranges (B1:C4 and B5:C7) eg as here.

edited May 23 '17 at 12:02

Community

1
1

answered May 12 '17 at 07:18

pnuts

58,317
11
87
139

How to Summarise Positive / Negative Number without Adding New Column in Excel Pivot Table

2 Answers2