Pivot Table: Count number of column values within each unique sub_class expressed by class

[filtrator]

Hi Mr. Excel,

I need some Excel Pivot Table help, I'm not quite sure how to express this question in technically appropriate language so I've create the below illustration.

Considering the below table, is there an elegant way to count (not sum) using a pivot table, the number of seeded foods ('food_seeded') in each food_class for every unique food_sub_class?

food_class	food_sub_class	food_seeded	unique_food_sub_class
fruit	granny smith apple	seeded	1
fruit	granny smith apple	seedless	0
fruit	macintosh apple	seeded	1
fruit	macintosh apple	seedless	0
fruit	granny smith apple	null	0
fruit	macintosh apple	null	0
bread	rye	seeded	1
bread	rye	seedless	0
bread	rye	null	0
bread	sourdough	null	1
vegetable	carrot	seeded	1
vegetable	carrot	seedless	0
vegetable	carrot	null	0
fruit	pear	seeded	1
fruit	pear	null	0
fruit	tomato	seeded	1
fruit	tomato	seedless	0
vegetable	eggplant	null	1
fruit	delicious apple	seeded	1
fruit	bakersfield apple	seedless	1
fruit	bakersfield apple	null	0
fruit	new york apple	seedless	1

<tbody>
</tbody>

Thank-you!

 

[Jerry Sullivan]

Hi filtrator,

If I'm understanding your objective, this is one way...

Create a PivotTable using your source data.
In the PivotTable field list, drag the food_sub_class field into the Row Labels area.

Next hold the Ctrl key down and drag the food_sub_class field again, but this time drop it into the Values (or Datafield) area. The field should automatically be renamed to "Count of food_sub_class".

The counts displayed reflect the total count of all records for each unique food_sub_class. The last step is to filter the report to just show the count for the records that have "seeded" in the food_seeded field.

One way to do that would be to place the food_seeded field in the Report Filters area of the PivotTable field list. Then filter to just show "seeded".

 

[filtrator]

Thanks a million for the response Jerry,

Apologies; I don't think I worded my question succinctly enough -- I think I need to go one step beyond what you've illustrated.

I want to know for each food_class:

• Fruit
• Vegetable
• Bread

The total count of each food_seeded value:

• Seeded
• Seedless
• Null

Belonging to each unique food_sub_class:

Bakersfield Apple

Carrot

Delicious Apple

Eggplant

Fuji Apple

Granny Smith Apple

Macintosh Apple

New York Apple

Pear

Rye

Sourdough

Tomato

<colgroup>

</colgroup><tbody>
</tbody>

Would something like a Count formula applied to a Calculated Field nested inside a Pivot be possible?

 

[filtrator]

To be even more succinct -- I want to know if each unique food_sub_class contains a match against each food_seeded value. I'm dealing with a document with thousands of lines, so this fruit and vegetable illustration is more a summary of the logic that I need to summarize.

 

[Jerry Sullivan]

You can do that without the use of calculated fields.

Create a PivotTable using your source data.

In the PivotTable field list, drag the food_class field into the Row Labels area.

In the PivotTable field list, drag the food_sub_class field into the Row Labels area.

Next hold the Ctrl key down and drag the food_sub_class field again, but this time drop it into the Values (or Datafield) area. The field should automatically be renamed to "Count of food_sub_class".

In the PivotTable field list, drag the food_seeded field into the Column Labels area.

 

[filtrator]

Hi Jerry,

I've created an example of what I'd like to summarize more elegantly -- how best to attach it?

Best,

 

[Jerry Sullivan]

A screen shot is preferred so others can benefit without downloading. You can upload an image or excel file to a sharing site and post a link.

 

[filtrator]

Roger that; dropbox screenshot below:

http://www.dropbox.com/s/h7o8kafaav7y961/Screen Shot 2015-10-08 at 11.58.14 AM.png?dl=0

 

[Jerry Sullivan]

Can you explain which parts of that image represent your desired result?

 

[filtrator]

Can you explain which parts of that image represent your desired result?

I'd like to understand if there's a more elegant way to summarize the top pivot table (G2:H6) and its associated formulas (I2:N6) in one pivot table that nests the counts of the formulas.

Formulas are currently counting the number of food_seeded attributes in columns I, J, K for the below food_class pivot, filtered by food_class below.