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

 

[excelhjalp]

How about

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

Amazing! This works! Thanks a lot Fluff!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Legacy 143009]

How about

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

Hi! What about a non-365 solution? I wonder what I did wrong :/

 

[Fluff]

With your formula, for Chair the Index part will return an ID of 1 & so will never look at ID 3.