Summing data based on multiple criteria

[VinnyJ]

I'm trying to create a summary table which identifies and sums data elsewhere in the same sheet according to two criteria.

In the data table:

Column A lists contains the names of a list of funds.
Column B details the asset class categorisation of each fund.
Column C details the strategy categorisation of each fund
Column D details the amount allocated to that fund

I would like to create a matrix (either within the same worksheet or on another sheet in the same document) where the asset class categorisations are shown in the left hand column, the strategy categorisations are listed along the row across the top, and the total amount allocated to each asset class and strategy is displayed within the table.

I've looked into a numerous ways it might be done (using SUMIF, INDEX-MATCH, SUMPRODUCT, etc and various combinations) but nothing I've tried has worked. I don't know VBA so I would really like to stick with a formula based answer if possible. Any assistance appreciated.

 

[danzon]

Use a pivot table... this is exactly what they are for

 

[MrKowz]

Welcome to the forums!

Try a pivot table! They might sound scary, but they are incredibly easy to use! When the Pivot Table wizard shows up where you can select what data to put in what field, put Asset Class Categorization in the "Row Fields" area, put Strategy Categorizations in the "Column Fields" area, and put Amount Allocated in the "Data Fields"

 

[danzon]

result looks like this

 

[VinnyJ]

Thanks guys, that's exactly what I needed. Much appreciated.