1

I have the following table:

FRUIT  COLOR  TOT
Apple  red    2
Apple  red    14
Apple  green  22
Grape  black  1
Grape  black  4
Grape  black  4

I want to dynamically populate a summary sheet like this:

FRUIT  COLOR  TOT
Apple  red    16
Apple  green  22
Grape  black  9

So essentially I need to extract with a dynamic formula distinct categories (fruit) and the relative subcategories (color), so that if I add in the first table a new row with:

Banana  yellow  5

It will be automatically added to the summary.

Kinda like this question, but I need it dynamic and with also sub-category.. With only categories I already succeeded in doing it, the real problem are subcategories..

piko
  • 1,020
mattogol
  • 13

1 Answers1

2

You don't need any formula. Just use the tools that Excel provides.

  1. Select your data
  2. Click Insert > Table
  3. Click on one of the cell of the table
  4. Click Insert > Pivot Table
  5. In the rows, add the fields FRUIT and COLOR
  6. In the values, add the field TOT
  7. Configure the layout of the pivot table to fit your needs

When you add a new value to your data table (preferably at the end of the table), the table will automatically extend. Then, right click on the pivot table and click on Refresh.

Alternatively, you can update the pivot table using the Refresh All button in the Data tab.

Documentation:

  1. Create a PivotTable to analyze worksheet data (Microsoft)
  2. Pivot Tables in Excel

Example:

enter image description here

piko
  • 1,020