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:

Customer-ID	Entry Date	Exit Date	Match-ID
123	01. January 2016	06. May 2016	333
234	15. April 2014	23. December 2015	444
...	...	...	...

<tbody>
</tbody>

Sheet 2:

Customer-ID	Spending 2016	Spending 2015	Spending ...
333	5.000,00	4.000,00	...
444	2.000,00	3.000,00	...
...	...	...	...

<tbody>
</tbody>

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:

	2016	2015	...
Match-Spending	5.000,00	3.000,00

<tbody>
</tbody>

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
1	Match-Spending	2016	2015
2		5000	3000

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula B2 =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)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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.