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]

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.