Data Consolidation by Position is a little more rigid than Three Dimensional Formulas because it requires the layout and structure of the data on each of the worksheets involved in the consolidation be identical.
And, for Data Consolidation by Position to work properly, 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.
Consolidating Data by Position
Click in the first summary data cell on your summary worksheet
Click the Consolidate command in the Data Tools group on the Data tab
Figure 21. Consolidate Data by Position 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. Click OK