Without Pivot Table need the summary using functions

Question

Hi,

I have a data in 1 sheet and showing the summary in other sheet. My requirement is to sort the specific field and show in summary. Pls. refer the excel sheet for more details on my requirement. Thank you.

I have uploaded the excel in the drive,

https://docs.google.com/spreadsheets/d/1nfeUSu-4Nd_cucVlCiPQIEjBphqzRhhD/edit?usp=sharing&ouid=101018147687121740410&rtpof=true&sd=true

Answer 1

OK, I stand corrected, your source data is already in good format. And the "Summary" you displayed is exactly a PivotTable in Excel.

.

If you add a new fruit, in a pivottable it will appear.

.

.

Click on the "Y" in the slicer will exactly reproduce your example

PS: here is link to my example file https://1drv.ms/x/s!AlV7uGd6SkRXgR4GgdVb5RyRCiSb

Answer 2

OK, I stand corrected, your source data is already in good format. And the "Summary" you displayed is exactly a PivotTable in Excel.

.

If you add a new fruit, in a pivottable it will appear.

.

.

Click on the "Y" in the slicer will exactly reproduce your example

Answer 3

I was going to ask the same question, so I'll answer your concern.

.

If your data is set up in the "right" way you don't have to worry about manually adding new categories. The pivot will do it automagically!

.

This may take some manipulation in PowerQuery. I suspect you'll have to "unpivot" your source data. A simple task, once you are aware of the concept and function.

.

I'll get back to you in a while after I look at your example data.

Answer 4

Hello, Thank you so much for your reply.

I am avoiding Pivot table is because, we need to create 15 - 20 summaries from the data sheet. Also, if i get new fields in data sheet I may need to change the range, Pivot table format will change, also i need to manually add the new field, refreshing the pivot tables etc., so to avoid all these issues can we have some functions which will go check the base data and take top 10 or top 15 values and the respective fields as i mentioned in the excel sheet and show up the summary? Or any macro code to get top 15 or top 10 values? Pls. advise.

Answer 5

Why reinvent the wheel? A pivot table does exactly what you want...