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

 

[Fluff]

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[excelhjalp]

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

Yup, see the screenshot. It looks like it workes for some part, but does not calculate all days correctly.

 

[Legacy 143009]

This doesn't make sense? Are you including upper-bound dates or not?
In your first example, you were not including the upper-bound. See M4

My formula can't work if both dates are included.

 

[excelhjalp]

This doesn't make sense? Are you including upper-bound dates or not?
In your first example, you were not including the upper-bound. See M4
View attachment 80199
My formula can't work if both dates are included.

Ah sorry. Yes, it should reserve the products for both dates.

 

[excelhjalp]

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I'm using 365. I've updated my profile, thanks for letting me know!

 

[Legacy 143009]

Ok this is my last try. This is not an array formula. you can paste normally. However, you are using 365 so you can paste any formula normally

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

I am not sure if this counts multiple instances. Maybe you should wait for @Fluff 's solution.

 

[excelhjalp]

Ok this is my last try. This is not an array formula. you can paste normally. However, you are using 365 so you can paste any formula normally

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

,I am not sure if this counts multiple instances. Maybe you should wait for @Fluff 's solution.

Thanks for all the tries Flashbond! I really appreciate it. This error shows up though when I try this. perhaps there's a bracket in the wrong place?

 

[Legacy 143009]

Yes, I updated the formula. Also some semicolons were left because of the regional settings of my excel. Please try the updated formula in post #16.
I believe the third time's the charm.

 

[excelhjalp]

Yes, I updated the formula. Also some semicolons were left because of the regional settings of my excel. Please try the updated formula in post #16.
I believe the third time's the charm.

It looks like it works, unless the same product is in more than one ID, so f.ex. the chairs are counted wrong. Again, I really appreciate all the efford mate! If you give up (like me) then hopefully @Fluff can shed some light into this :D

 

[Fluff]

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)