Fun inventory problem

[excelhjalp]

Hello!

I have a problem I can't seem to solve. This is for a rental busniess that rents out products and gets them back at a certain date.
I have four tables.

Table A: A daterange based on ID, that's a key between table A and table B
Table B: A table where one or more products can be reserved as well as how many of them. Each ID can be made of multiple products here.
Table C: An Inventory based on dates. Here a formula would be needed for the third column, inventory. It should calculate how many products are available at a given date based on Table A, B and D. It should take the total count of products from table D, and subtract the total number reserved in tables A and B and give a result, for each day specified.
Table D: A simple table where the total number of products is stated.

So the problem is: What formula can I use to calculate total products of each type in stock at a given date in the third column in Table C?

Thanks for those who give this a shot! :D

masterskjal2.3.xlsb.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Table A					Table B					Table C						Table D (Total inventory)
2	ID	Date from	Date to			ID	Product	Count of product			Product	Date	Inventory				Football	3
3	1	1.1.2022	2.1.2022			1	Football	2			Football	1.1.2022	1				Pants	5
4	2	1.1.2022	3.1.2022			1	Chair	2			Football	2.1.2022	3				Chair	10
5	3	3.1.2022	4.1.2022			2	Pants	3			Football	3.1.2022	3
6						3	Chair	6			Football	4.1.2022	3
7											Football	5.1.2022	3
8											Pants	1.1.2022	5
9											Pants	2.1.2022	2
10											Pants	3.1.2022	2
11											Pants	4.1.2022	5
12											Pants	5.1.2022	5
13											Chair	1.1.2022	8
14											Chair	2.1.2022	8
15											Chair	3.1.2022	6
16											Chair	4.1.2022	6
17											Chair	5.1.2022	10
18
19
20
21
Sheet1

 

[Legacy 143009]

Is there any possibility that B4 can be 2.1.2022?

 

[excelhjalp]

Is there any possibility that B4 can be 2.1.2022?

Whoops! You're correct. So sorry about that!

 

[Legacy 143009]

Does ID column in Table B refer to Dates table? In other words, is it a foreign key for Table A?

 

[excelhjalp]

Is ID column in Table B refers to Dates table? In other words, is it a foreign key for Table A?

Yes, you're correct. It's used to see which products and how many of them where reserved in B during the dates in A

 

[Legacy 143009]

Here is an INDEXeption formula. I am sure there is a more efficient way. M3:

=VLOOKUP(K3,$Q$2:$R$4,2,0)-IFERROR(INDEX($H$3:$H$6,MATCH(1,($G$3:$G$6=K3)*(L3>=VLOOKUP(INDEX($F$3:$F$6,MATCH(K3,$G$3:$G$6,0)),$A$3:$C$5,2,0))*(L3<VLOOKUP(INDEX($F$3:$F$6,MATCH(K3,$G$3:$G$6,0)),$A$3:$C$5,3,0)),0)),0)

This is an array formula. You must press Ctrl+Shift+Enter together after pasting if you are not using XL365.

 

[excelhjalp]

Here is an INDEXeption formula. I am sure there is a more efficient way. M3:

=VLOOKUP(K3,$Q$2:$R$4,2,0)-IFERROR(INDEX($H$3:$H$6,MATCH(1,($G$3:$G$6=K3)*(L3>=VLOOKUP(INDEX($F$3:$F$6,MATCH(K3,$G$3:$G$6,0)),$A$3:$C$5,2,0))*(L3<VLOOKUP(INDEX($F$3:$F$6,MATCH(K3,$G$3:$G$6,0)),$A$3:$C$5,3,0)),0)),0)

This is an array formula. You must press Ctrl+Shift+Enter together after pasting if you are not using XL365.

Thanks Flashbond! However, it looks like this formula does not account for scenarious that contain more that one instance of products in table B, if I'm not mistaken.

 

[Legacy 143009]

How about this one? A slightly shorter version:

=VLOOKUP(K3,$Q$2:$R$4,2,0)-SUMIFS($H$3:$H$6,$G$3:$G$6,K3,$F$3:$F$6,INDEX($A$3:$A$5,MATCH(1,(L3>=$B$3:$B$5)*(L3<$C$3:$C$5),0)))

This may handle multiple instances also. The previous code was matching only the first instances where the conditions were met. SUMIFS function should include every instance which fits the condition.

 

[excelhjalp]

How about this one? A slightly shorter version:

=VLOOKUP(K3,$Q$2:$R$4,2,0)-SUMIFS($H$3:$H$6,$G$3:$G$6,K3,$F$3:$F$6,INDEX($A$3:$A$5,MATCH(1,(L3>=$B$3:$B$5)*(L3<$C$3:$C$5),0)))

This may handle multiple instances also. The previous code was matching only the first instances where the conditions were met. SUMIFS function should include every instance which fits the condition.

Hmm, something's not right here. This is giving me incorrect results.

 

[Legacy 143009]

Are you pasting as array formula? It was ok in my sheet