Sum based on multiple changing criteria

[markch]

Hi Everybody,

I'm not sure how to phrase this one, so please bare with me whilst I do my best to explain!

I've got a complicated spreadsheet for tracking project expenditure by stage, time, type of spend etc. In this spreadsheet you can choose which months you will be in which particular stages. In the detail, you can list certain spend types against each month.

I want to be able to pull the stage-based spend in to a summary report to show how much is spent in each stage broken down by spend type, such as this:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Concept[/TD]
[TD]Initiation[/TD]
[TD]Delivery [/TD]
[TD]Close[/TD]
[/TR]
[TR]
[TD]Team 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Team 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Team 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Team 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The source table looks something like this:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Concept[/TD]
[TD]Initiation[/TD]
[TD]Delivery[/TD]
[TD]Close[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Jan-17[/TD]
[TD]Feb-17[/TD]
[TD]Mar-17[/TD]
[TD]Apr-17[/TD]
[TD]May-17[/TD]
[TD]Jun-17[/TD]
[/TR]
[TR]
[TD]Type 1[/TD]
[TD][/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Type 2[/TD]
[TD][/TD]
[TD][/TD]
[TD]200[/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Type 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]50[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Type 1[/TD]
[TD][/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Type 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Type 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The stages across the top (Concept, Initiation, etc.) are set from a 'Setup' worksheet earlier in the workbook where you choose which stage you will be in during each month. There can be multiples of each type, as there could be different spends under each type. In the most complicated sheet we have eight spend types and five stages.

I could use formulas to individually add up all scenarios, i.e. Concept-Type 1, Concept-Type 2, Initiation-Type 1, Initiation-Type 2, etc. and pick the correct value for the relevant summary cell, but this seems a very long-winded way of doing it.

Is there a formula to look and summarise these spend types per stage? It needs to sum cells where a cell to the left equals a certain value as well a cell above. I've tried searching but I'm not sure how to search for the correct thing.

Thanks in advance for any help,

Mark.

 

[Special-K99]

"Team 1" for example only appears in your first table.
It does not appear in your second table nor does the word Team appear anywhere in your description.

So how will Excel know how to identify data belonging to Team 1?
Or any Team for that matter?

 

[markch]

Apologies, that was a typo, it will be the same in both tables. I'll edit the post to show this.

 

[markch]

Unfortunately I don't seem to be able to edit the post..

 

[Special-K99]

Post a new corrected table

 

[markch]

Corrected as requested.

I want to be able to pull the stage-based spend in to a summary report to show how much is spent in each stage broken down by spend type, such as this:
[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Concept[/TD]
[TD]Initiation[/TD]
[TD]Delivery[/TD]
[TD]Close[/TD]
[/TR]
[TR]
[TD]Type 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Type 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Type 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Type 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


The source table looks something like this:
[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Concept[/TD]
[TD]Initiation[/TD]
[TD]Delivery[/TD]
[TD]Close[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Jan-17[/TD]
[TD]Feb-17[/TD]
[TD]Mar-17[/TD]
[TD]Apr-17[/TD]
[TD]May-17[/TD]
[TD]Jun-17[/TD]
[/TR]
[TR]
[TD]Type 1[/TD]
[TD][/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Type 2[/TD]
[TD][/TD]
[TD][/TD]
[TD]200[/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Type 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]50[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Type 1[/TD]
[TD][/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Type 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Type 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thanks,

Mark.

 

[Special-K99]

Mm, having trouble with this.
Started doing a SUMPRODUCT but the ranges need to be the same size.

Still looking at this...

 

[markch]

Thanks, I appreciate you trying to help!