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.

Answer 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.