How to create calculated column in excel pivot table?

Question

I have 3 columns in my excel sheet - ID, Version, Material No.

for ex -

and i want the pivot table to be like -

How can i create the third calculated column because it involves two aggregation function to create it. I am looking for count of maximum version so i need MAX function also, not count of unique values.

For one particular ID (say 1) the third column should calculate the count of material no which has maximum version. For ID 1 highest version is 2 and only material no 101 is there. So third column should be 1. Similarly for ID 2 max Version is 2 and 3 material (103,104,105) has these version so third columns should have value as 3. — Prasoon Pandey
– Prasoon Pandey, Commented May 19, 2017 at 5:54
Possible duplicate of Simple Pivot Table to Count Unique Values . If I understand your description correct. — Andreas
– Andreas, Commented May 19, 2017 at 6:20
No Sir, this is not exact duplicate of that. Thanks for your suggestion. — Prasoon Pandey
– Prasoon Pandey, Commented May 19, 2017 at 7:16

g.kov · Accepted Answer · 2017-05-19 11:27:29Z

1

Consider a solution based on worksheet functions.

Assuming the table is in B5:D14, enter an array formula, which counts unique IDs

=SUM(N($B$5:$B$14<>$B$4:$B$13))

into G2, enter

COUNT($B$5:$B$14)-G2

in H2.

Enter an array formula ( type Ctrl+Shift+Enter instead of just Enter), which collects unique IDs on top and fill the rest of the output lines with blanks:

=IFERROR(INDEX($B$1:$B$14,SMALL(N($B$5:$B$14<>$B$4:$B$13)*ROW($B$5:$B$14),ROW(B5)-ROW($B$4)+$H$2))*N(SMALL(N($B$5:$B$14<>$B$4:$B$13)*ROW($B$5:$B$14),ROW(B5)-ROW($B$4)+$H$2)>0),"")

in G5, another array formula

=IF(LEN(G5)>0,MAX(N($B$5:$B$14=G5)*$C$5:$C$14),"")

in H5 and one more array formula

=IF(LEN(G5)>0,SUM(($B$5:$B$14=G5)*($C$5:$C$14=H5)),"")

in I5, select range G5:I5 and drag/copy it down.

answered May 19, 2017 at 11:27

g.kov

3232 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to create calculated column in excel pivot table?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related