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?


[TABLE="width: 500"]
<tbody>[TR]
[TD]food_class[/TD]
[TD]food_sub_class[/TD]
[TD]food_seeded[/TD]
[TD]unique_food_sub_class[/TD]
[/TR]
[TR]
[TD]fruit[/TD]
[TD]granny smith apple[/TD]
[TD]seeded[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]fruit[/TD]
[TD]granny smith apple[/TD]
[TD]seedless[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]fruit[/TD]
[TD]macintosh apple[/TD]
[TD]seeded[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]fruit[/TD]
[TD]macintosh apple[/TD]
[TD]seedless[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]fruit[/TD]
[TD]granny smith apple[/TD]
[TD]null[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]fruit[/TD]
[TD]macintosh apple[/TD]
[TD]null[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]bread[/TD]
[TD]rye[/TD]
[TD]seeded[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]bread[/TD]
[TD]rye[/TD]
[TD]seedless[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]bread[/TD]
[TD]rye[/TD]
[TD]null[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]bread[/TD]
[TD]sourdough[/TD]
[TD]null[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]vegetable[/TD]
[TD]carrot[/TD]
[TD]seeded[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]vegetable[/TD]
[TD]carrot[/TD]
[TD]seedless[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]vegetable[/TD]
[TD]carrot[/TD]
[TD]null[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]fruit[/TD]
[TD]pear[/TD]
[TD]seeded[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]fruit[/TD]
[TD]pear[/TD]
[TD]null[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]fruit[/TD]
[TD]tomato[/TD]
[TD]seeded[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]fruit[/TD]
[TD]tomato[/TD]
[TD]seedless[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]vegetable[/TD]
[TD]eggplant[/TD]
[TD]null[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]fruit[/TD]
[TD]delicious apple[/TD]
[TD]seeded[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]fruit[/TD]
[TD]bakersfield apple[/TD]
[TD]seedless[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]fruit[/TD]
[TD]bakersfield apple[/TD]
[TD]null[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]fruit[/TD]
[TD]new york apple[/TD]
[TD]seedless[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]


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:

[TABLE="width: 139"]
<colgroup>

</colgroup><tbody>[TR]
[TD]

• Bakersfield Apple

[/TD]
[/TR]
[TR]
[TD]

• Carrot

[/TD]
[/TR]
[TR]
[TD]

• Delicious Apple

[/TD]
[/TR]
[TR]
[TD]

• Eggplant

[/TD]
[/TR]
[TR]
[TD]

• Fuji Apple

[/TD]
[/TR]
[TR]
[TD]

• Granny Smith Apple

[/TD]
[/TR]
[TR]
[TD]

• Macintosh Apple

[/TD]
[/TR]
[TR]
[TD]

• New York Apple

[/TD]
[/TR]
[TR]
[TD]

• Pear

[/TD]
[/TR]
[TR]
[TD]

• Rye

[/TD]
[/TR]
[TR]
[TD]

• Sourdough

[/TD]
[/TR]
[TR]
[TD]

• Tomato

[/TD]
[/TR]
</tbody>[/TABLE]

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.