counting data in three arrays

[GraceTilly]

As a company, we provide uniforms for our staff
Trousers, T-Shirts, Boots, Shirts, caps, jumpers etc...obviously each come in various sizes, S,M,L,XL, boots in various sizes, 7,8,9, etc.....
What I am trying to do is create a stock sheet that tells me how much I have of each item and what size based on my original stock and what has been handed out.
Example In Cell A3 is the word Shirt and in b3,c3,d3,e3 would be Small, Medium,Large, extra large. and in the cells b4,c4,d4,e4, would be a numeric saying how many of those I have.
On a sheet within the worksheet I catalogue what comes in and what goes out so at any given time I know my stock position.
The details are entered thus, C10,date, D10,name, E10,product (ie T-shirt), F10,size and G10,quantity.
So on the at sheet I have given out 1 medium T-shirt , SO IN e10 the word T-Shirt in f10 the letter M and in G10, 1
What I want to do is use something like xlookup to determine the product, the size and other quantity and take it from my existing stock, but I am struggling to use three arrays, product, size and quantity???

 

[coulombevin]

Hi GraceTilly,

Imagine you have a sheet 'current_inventory', 'uniforms_out' (what is given to your employees) and 'uniforms_in' (your orders in). Here's what I would do:

-English version-

=SUMIFS(uniforms_in!$D:$D,uniforms_in!$B:$B,$A3,uniforms_in!$C:$C,B$2)-SUMIFS(uniforms_out!$E:$E,uniforms_out!$C:$C,$A3,uniforms_out!$D:$D,B$2)

-French version-

=SOMME.SI.ENS(uniforms_in!$D:$D;uniforms_in!$B:$B;$A3;uniforms_in!$C:$C;B$2)-SOMME.SI.ENS(uniforms_out!$E:$E;uniforms_out!$C:$C;$A3;uniforms_out!$D:$D;B$2)

This formula make the sum of your order (in) based on uniform and size minus the total given based on uniform and size.

sheet: 'current_inventory'

Classeur1
	A	B	C	D	E	F	G	H	I	J
1	Stock left
2	Uniforms/sizes	S	M	L	XL	7	8	9	10	11
3	Trousers	50	17	52	68	0	0	0	0	0
4	T-Shirts	51	70	44	43	0	0	0	0	0
5	Boots	0	0	0	0	65	50	42	77	35
6	Shirts	28	92	69	52	0	0	0	0	0
7	Caps	49	96	100	31	0	0	0	0	0
8	Jumpers	22	55	21	40	0	0	0	0	0
current_inventory

Cell Formulas
Range	Formula
B3:J8	B3	=SUMIFS(uniforms_in!$D:$D,uniforms_in!$B:$B,$A3,uniforms_in!$C:$C,B$2)-SUMIFS(uniforms_out!$E:$E,uniforms_out!$C:$C,$A3,uniforms_out!$D:$D,B$2)

sheet: 'uniforms_out'

Classeur1
	A	B	C	D	E
1	date	name	product	size	quantity
2	2024-10-21	John Doe 6	Trousers	L	1
3	2024-10-23	John Doe 1	T-Shirts	S	3
4	2024-10-27	John Doe 2	Boots	7	3
5	2024-10-30	John Doe 7	Shirts	M	3
6	2024-12-12	John Doe 1	Caps	S	3
7	2024-12-21	John Doe 6	Jumpers	L	1
8	2025-01-01	John Doe 4	Trousers	S	3
9	2025-01-08	John Doe 7	Caps	M	2
10	2025-01-09	John Doe 3	Trousers	M	3
11	2025-01-10	John Doe 1	Jumpers	S	2
12	2025-01-21	John Doe 7	Boots	7	3
13	2025-01-21	John Doe 6	T-Shirts	L	3
14	2025-01-22	John Doe 6	Boots	11	2
uniforms_out

sheet: 'uniforms_in'

Classeur1
	A	B	C	D
1	date	product	size	quantity
2	2024-10-20	Trousers	S	53
3	2024-10-20	Trousers	M	20
4	2024-10-20	Trousers	L	53
5	2024-10-20	Trousers	XL	68
6	2024-10-20	T-Shirts	S	54
7	2024-10-20	T-Shirts	M	70
8	2024-10-20	T-Shirts	L	47
9	2024-10-20	T-Shirts	XL	43
10	2024-10-20	Boots	7	71
11	2024-10-20	Boots	8	50
12	2024-10-20	Boots	9	42
13	2024-10-20	Boots	10	77
14	2024-10-20	Boots	11	37
15	2024-10-20	Shirts	S	28
16	2024-10-20	Shirts	M	95
17	2024-10-20	Shirts	L	69
18	2024-10-20	Shirts	XL	52
19	2024-10-20	Caps	S	52
20	2024-10-20	Caps	M	98
21	2024-10-20	Caps	L	100
22	2024-10-20	Caps	XL	31
23	2024-10-20	Jumpers	S	24
24	2024-10-20	Jumpers	M	55
25	2024-10-20	Jumpers	L	22
26	2024-10-20	Jumpers	XL	40
uniforms_in

Bests regards,

Vincent

 

[GraceTilly]

Sorry for the delayed response, I have been going over your reply and now it is working perfectly. Thank you so much for your time and efforts

 

[coulombevin]

Hi GraceTilly,

No problem. If there's somthing else don't hesitate to create a new thread! Also, if you could write the answer as solution, it would help me follow where I'm at in my followed threads.

Bests regards,

Vincent