How to pivot based on three fields?

ArviLaanemets · #1 05-09-2018, 01:36 AM

You can't get cell values as Pivot Table values - they must be either Sum, Count, Average, Max, Min, Product, Numbers count, StdDev, StdDevp, Var or Varp of COLUMNS values.

As Pivot Table is 2-dimensional as max, with 3 fields you have to decide, do ROWS or COLUMNS have 2 fields to group. And as values you have to use Count - there is now way you can sum letters, and I can't see a point in summing F1 values too.

You can get the result more like you want, when you add a calculated column where you concatenate values in F1 and F2

Jamal NUMAN · #2 05-09-2018, 09:31 AM

Quote:

Originally Posted by ArviLaanemets

You can't get cell values as Pivot Table values - they must be either Sum, Count, Average, Max, Min, Product, Numbers count, StdDev, StdDevp, Var or Varp of COLUMNS values.

As Pivot Table is 2-dimensional as max, with 3 fields you have to decide, do ROWS or COLUMNS have 2 fields to group. And as values you have to use Count - there is now way you can sum letters, and I can't see a point in summing F1 values too.

You can get the result more like you want, when you add a calculated column where you concatenate values in F1 and F2

Thank you ArviLaanemets for the answer.

I’m not sure if there is other tool that can help performing deriving the result table out of the given table as shown in the screenshot below. It appears that Pivot tool has no such capacity

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
cannot drag fields to pivot table	jalea148	Excel	5	07-31-2017 04:03 AM
Remove any pivot table fields with a blank entry	Ramtrap	Excel Programming	0	04-14-2017 07:00 AM
Show calculated fields within the Pivot Table	John in DSM	Excel	2	09-07-2016 02:12 PM
Pivot table - I cant display item labels when no fields in value area	differentdrummer	Excel	0	12-12-2013 05:13 PM
Pivot Table's fields	janis129	Excel	7	03-02-2010 03:16 PM