Summing up values based on multiple criterias and including lookups across multiple sheets

[phiman13]

Hey guys,

I'm facing a rather tricky issue:

I have two data sheets and one output sheet.

Sheet 1:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Customer-ID[/TD]
[TD]Entry Date[/TD]
[TD]Exit Date[/TD]
[TD]Match-ID[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]01. January 2016[/TD]
[TD]06. May 2016[/TD]
[TD]333[/TD]
[/TR]
[TR]
[TD]234[/TD]
[TD]15. April 2014[/TD]
[TD]23. December 2015[/TD]
[TD]444[/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Customer-ID[/TD]
[TD]Spending 2016[/TD]
[TD]Spending 2015[/TD]
[TD]Spending ...[/TD]
[/TR]
[TR]
[TD]333[/TD]
[TD]5.000,00[/TD]
[TD]4.000,00[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]444[/TD]
[TD]2.000,00[/TD]
[TD]3.000,00[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
</tbody>[/TABLE]

Now to the problem at hand.
I need a formula for that allows me to sum up the spending of the customers from sheet 2 based on two criteria:
a) Their IDs have to be mentioned as "Match-IDs" in sheet 1.
b) The matched customer from sheet 1 has to have been a customer in the respective year (i. e. when summing up 2016 values, only sum those where the respective matching customer has an entry date < 31. Dec 2016 and an exit date > 01. Jan 2016.

Output Sheet:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]2016[/TD]
[TD]2015[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]Match-Spending[/TD]
[TD]5.000,00[/TD]
[TD]3.000,00[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I suspect that this is somehow solvable either by a combination of SUMIFS and INDEX MATCH ...

Any help would be greatly appreciated!

Cheers

 

[Eric W]

Try:

	A	B	C

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]Match-Spending[/TD]
[TD="align: right"]2016[/TD]
[TD="align: right"]2015[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]3000[/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=SUMPRODUCT(Sheet2!B2:B10,--(COUNTIFS(Sheet1!$D$2:$D$20,Sheet2!$A$2:$A10,Sheet1!$B$2:$B$20,"<="&DATE(B1,12,31),Sheet1!$C$2:$C$20,">="&DATE(B1,1,1))>0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Based on how your dates and numbers are formatted, you may need to change all the commas in this formula to semicolons, to allow for the regional differences.