Help with creating pivot to display number of occurrences of events

Hey guys!

I'm new to this forum and hoping somebody can help me out!

I have a table whereby Column A (Food) lists certain types of foods and then from Column B through to Z I have the heading for each column labelled as a Meal Type (eg., BBQ, Vegetarian, Cake etc.)

Within the dataset, I have COUNTIF formulas linking to raw data in another spreadsheet to calculate the number of times Food X appears in Meal Type X etc. etc.

Now that I have the data in front of me with conditional formatting to highlight the Foods that > 0 within a Meal Type - I'm stuck with creating a pivot table so that I can just filter a Meal Type so that it tells me which Food has the greatest value in that particular category.

For example, I want to be able to filter BBQ and the results should display Sausages (since Sausages had the highest number of occurrences under BBQ).

I think the problem may be that my Column A is labelled appropriately (ie., Food) but B onwards, the Meal Types headings are labelled as the actual Meal Types so when I go to create a pivot table, I I have to manually select each meal type (and there's a huge amount of them).

If anybody can provide some assistance it would be much much appreciated! Thanks so much

I think the problem may be that my Column A is labelled appropriately (ie., Food) but B onwards, the Meal Types headings are labelled as the actual Meal Types so when I go to create a pivot table, I I have to manually select each meal type (and there's a huge amount of them).

Yes, I'm afraid you're right. Instead of a short and wide matrix you should have a long and slim table where Col A is Food, Col B is Meal Type, and Col C holds the result of your COUNTIF(S) formula {relative to values in Columns A & B [same row]}, you can then use Col B as a Page Field for ex. -- i.e. will be much more flexible.