Count how many times a product sold in a date range

[bobbieatendido]

Hi,

Is there a way to count how many times a product was sold in a specific date range?

ROW:
ProductCode A, ProductName B
Week 1 D2 to E2

List of products sold per day
A16-Date
B16-Product sold
C16-Product name

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1				Week1		Week2		Week3		Week4		Week1		Week2		Week3		Week4
2				01-Jan-23	08-Jan-23	08-Jan-23	15-Jan-23	15-Jan-23	22-Jan-23	22-Jan-23	29-Jan-23	29-Jan-23	05-Feb-23	05-Feb-23	12-Feb-23	12-Feb-23	19-Feb-23	19-Feb-23	26-Feb-23
3	Product Code	Product		In	Out	In	Out	In	Out	In	Out	In	Out	In	Out	In	Out	In	Out
4	1104-1001	Television			2
5	1104-1012	Computer			1
6	1104-1023	Helmet
7	1104-1024	Candle
8	1104-1033	Shawl
9	1104-1034	Fridge
10	1104-1035	Magnet
11	1104-1044	Chair
12	1104-1045	Balloon
13	1104-1055	Goggles
14
15	Date	Product Sold
16	05/01/2023	1104-1001	Television
17	05/01/2023	1104-1012	Computer
18	06/01/2023	1104-1001	Television
19	07/01/2023	1104-1045	Balloon
20	12/01/2023	1104-1023	Helmet
21	12/01/2023	1104-1024	Candle
22	14/02/2023	1104-1033	Shawl
23	02/02/2023	1104-1034	Fridge
24	03/02/2023	1104-1035	Magnet
25	04/02/2023	1104-1044	Chair
26	05/02/2023	1104-1044	Chair
27	06/01/2023	1104-1024	Candle
28	05/01/2023	1104-1023	Helmet
29	06/01/2023	1104-1024	Candle
30	09/02/2023	1104-1035	Magnet
31	10/02/2023	1104-1033	Shawl
Sheet1

Cell Formulas
Range	Formula
E2,G2,I2,K2,M2,O2,Q2,S2	E2	=D2+7
F2,H2,J2,L2,N2,P2,R2	F2	=E2
C16:C31	C16	=VLOOKUP(B16,$A$4:$B$13,2,FALSE)

 

[hajiali]

try this

=COUNTIFS($C$16:$C$31,$B4,$A$16:$A$31,">="&D$2,$A$16:$A$31,"<="&E$2)

then copy and paste to all the OUT columns

Book1
	A	B	C	D	E	F	G
1				Week1		Week2
2				1/1/2023	1/8/2023	1/8/2023	1/15/2023
3	Product Code	Product		In	Out	In	Out
4	1104-1001	Television			2		0
5	1104-1012	Computer			1		0
6	1104-1023	Helmet			1		1
7	1104-1024	Candle			2		1
8	1104-1033	Shawl			0		0
9	1104-1034	Fridge			0		0
10	1104-1035	Magnet			0		0
11	1104-1044	Chair			0		0
12	1104-1045	Balloon			1		0
13	1104-1055	Goggles			0		0
14
15	Date	Product Sold
16	1/5/2023	1104-1001	Television
17	1/5/2023	1104-1012	Computer
18	1/6/2023	1104-1001	Television
19	1/7/2023	1104-1045	Balloon
20	1/12/2023	1104-1023	Helmet
21	1/12/2023	1104-1024	Candle
22	2/14/2023	1104-1033	Shawl
23	2/2/2023	1104-1034	Fridge
24	2/3/2023	1104-1035	Magnet
25	2/4/2023	1104-1044	Chair
26	2/5/2023	1104-1044	Chair
27	1/6/2023	1104-1024	Candle
28	1/5/2023	1104-1023	Helmet
29	1/6/2023	1104-1024	Candle
30	2/9/2023	1104-1035	Magnet
31	2/10/2023	1104-1033	Shawl
Sheet4

Cell Formulas
Range	Formula
E2,G2	E2	=D2+7
F2	F2	=E2
E4:E13,G4:G13	E4	=COUNTIFS($C$16:$C$31,$B4,$A$16:$A$31,">="&D$2,$A$16:$A$31,"<="&E$2)
C16:C31	C16	=VLOOKUP(B16,$A$4:$B$13,2,FALSE)

 

[bobbieatendido]

Thank you sir!

 

[Peter_SSs]

Just checking: It may not be possible with your data but if an item is sold exactly on one of the dates in row 2 the item would be counted twice.

See the example below where I have used the suggested formula from above but changed the date in cell A23. You can see that the Fridge has been counted in both weeks

23 01 30.xlsm
	A	B	C	D	E	F	G
1				Week1		Week2
2				1/01/2023	8/01/2023	8/01/2023	15/01/2023
3	Product Code	Product		In	Out	In	Out
4	1104-1001	Television			2		0
5	1104-1012	Computer			1		0
6	1104-1023	Helmet			1		1
7	1104-1024	Candle			2		1
8	1104-1033	Shawl			0		0
9	1104-1034	Fridge			1		1
10	1104-1035	Magnet			0		0
11	1104-1044	Chair			0		0
12	1104-1045	Balloon			1		0
13	1104-1055	Goggles			0		0
14
15	Date	Product Sold
16	5/01/2023	1104-1001	Television
17	5/01/2023	1104-1012	Computer
18	6/01/2023	1104-1001	Television
19	7/01/2023	1104-1045	Balloon
20	12/01/2023	1104-1023	Helmet
21	12/01/2023	1104-1024	Candle
22	14/02/2023	1104-1033	Shawl
23	8/01/2023	1104-1034	Fridge
24	3/02/2023	1104-1035	Magnet
25	4/02/2023	1104-1044	Chair
26	5/02/2023	1104-1044	Chair
27	6/01/2023	1104-1024	Candle
28	5/01/2023	1104-1023	Helmet
29	6/01/2023	1104-1024	Candle
30	9/02/2023	1104-1035	Magnet
31	10/02/2023	1104-1033	Shawl
Count Sold

Cell Formulas
Range	Formula
E2,G2	E2	=D2+7
F2	F2	=E2
E4:E13,G4:G13	E4	=COUNTIFS($C$16:$C$31,$B4,$A$16:$A$31,">="&D$2,$A$16:$A$31,"<="&E$2)
C16:C31	C16	=VLOOKUP(B16,$A$4:$B$13,2,FALSE)

Wouldn't this slight adjustment be safer?

23 01 30.xlsm
	A	B	C	D	E	F	G
1				Week1		Week2
2				1/01/2023	8/01/2023	8/01/2023	15/01/2023
3	Product Code	Product		In	Out	In	Out
4	1104-1001	Television			2		0
5	1104-1012	Computer			1		0
6	1104-1023	Helmet			1		1
7	1104-1024	Candle			2		1
8	1104-1033	Shawl			0		0
9	1104-1034	Fridge			1		0
10	1104-1035	Magnet			0		0
11	1104-1044	Chair			0		0
12	1104-1045	Balloon			1		0
13	1104-1055	Goggles			0		0
14
15	Date	Product Sold
16	5/01/2023	1104-1001	Television
17	5/01/2023	1104-1012	Computer
18	6/01/2023	1104-1001	Television
19	7/01/2023	1104-1045	Balloon
20	12/01/2023	1104-1023	Helmet
21	12/01/2023	1104-1024	Candle
22	14/02/2023	1104-1033	Shawl
23	8/01/2023	1104-1034	Fridge
24	3/02/2023	1104-1035	Magnet
25	4/02/2023	1104-1044	Chair
26	5/02/2023	1104-1044	Chair
27	6/01/2023	1104-1024	Candle
28	5/01/2023	1104-1023	Helmet
29	6/01/2023	1104-1024	Candle
30	9/02/2023	1104-1035	Magnet
31	10/02/2023	1104-1033	Shawl
Count Sold (2)

Cell Formulas
Range	Formula
E2,G2	E2	=D2+7
F2	F2	=E2
E4:E13,G4:G13	E4	=COUNTIFS($C$16:$C$31,$B4,$A$16:$A$31,">"&E$2-7,$A$16:$A$31,"<="&E$2)
C16:C31	C16	=VLOOKUP(B16,$A$4:$B$13,2,FALSE)