When working with Data Consolidation by Category, the only thing you really need to be careful is to make sure that your column or row labels are identical from page to page, worksheet to worksheet. This includes spelling and capitalization.
And, just like Data Consolidation by Position, your data needs to be in list format. In other words, each column should have a header row and there should be no blank rows or blank columns.
Finally, each range that you want to include in the consolidation needs to have its own worksheet.
On the Data tab, in the Data Tools group, click Consolidate.
Consolidating Data by Category
Click in the label summary data cell on your summary worksheet
Click the Consolidate command in the Data Tools group on the Data tab
Figure 22. Consolidate Data by Category Dialog Box
3. Choose the function you’d like to use
4. Enter one of the ranges to include in the consolidation
5. Click Add
6. Repeat Steps 3 and 4 for each range in your consolidation
7. Select Top row or Left column to indicate the location of your labels
8. Click OK