I have the following table:

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:

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..


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.


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


enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.