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]

Okay, now I'm seeing the part you are having difficulty doing. The base Excel program doesn't have a distinct count form of aggregation.
To do that you can use an intermediate table (a bit clunky), PowerPivot (an add-in for Excel 2010 and provided free with the full version of Excel 2013), or do an SQL Query using MS Query.

Would any of those be an option you want to pursue?

 

[filtrator]

I'm on a Mac using Microsoft Office 2016 for Mac -- can you advise which of the above is the most compatible option?

I haven't used MS Query however I know how to use SQL -- if I used MS Query would it mean the analysis results would be stored outside of the document?

 

[Jerry Sullivan]

I don't have access to xl2016 for Mac, however I did find this article regarding how it allows queries.
https://blogs.office.com/2015/08/20/working-with-external-data-in-excel-2016-for-mac/

Speaking only from my experience with MS Query, the technique that is used is to start a blank new workbook and make an external query of your closed workbook with the data source.

Put the results of that query into a PivotTable in the new workbook. Then Move that PivotTable (Cut and Paste) it into the same workbook as the data source.

That way the PivotTable and data source can be in the same workbook. When the data is changed the Pivot can be refreshed to reflect the changes.

 

[filtrator]

Thanks Jerry, I will investigate.