Count how many times a product sold in a date range

bobbieatendido

New Member
Joined
Jul 13, 2016
Messages
21
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRS
1Week1Week2Week3Week4Week1Week2Week3Week4
201-Jan-2308-Jan-2308-Jan-2315-Jan-2315-Jan-2322-Jan-2322-Jan-2329-Jan-2329-Jan-2305-Feb-2305-Feb-2312-Feb-2312-Feb-2319-Feb-2319-Feb-2326-Feb-23
3Product CodeProductInOutInOutInOutInOutInOutInOutInOutInOut
41104-1001Television2
51104-1012Computer1
61104-1023Helmet
71104-1024Candle
81104-1033Shawl
91104-1034Fridge
101104-1035Magnet
111104-1044Chair
121104-1045Balloon
131104-1055Goggles
14
15DateProduct Sold
1605/01/20231104-1001Television
1705/01/20231104-1012Computer
1806/01/20231104-1001Television
1907/01/20231104-1045Balloon
2012/01/20231104-1023Helmet
2112/01/20231104-1024Candle
2214/02/20231104-1033Shawl
2302/02/20231104-1034Fridge
2403/02/20231104-1035Magnet
2504/02/20231104-1044Chair
2605/02/20231104-1044Chair
2706/01/20231104-1024Candle
2805/01/20231104-1023Helmet
2906/01/20231104-1024Candle
3009/02/20231104-1035Magnet
3110/02/20231104-1033Shawl
Sheet1
Cell Formulas
RangeFormula
E2,G2,I2,K2,M2,O2,Q2,S2E2=D2+7
F2,H2,J2,L2,N2,P2,R2F2=E2
C16:C31C16=VLOOKUP(B16,$A$4:$B$13,2,FALSE)
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
try this
Excel Formula:
=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
ABCDEFG
1Week1Week2
21/1/20231/8/20231/8/20231/15/2023
3Product CodeProductInOutInOut
41104-1001Television20
51104-1012Computer10
61104-1023Helmet11
71104-1024Candle21
81104-1033Shawl00
91104-1034Fridge00
101104-1035Magnet00
111104-1044Chair00
121104-1045Balloon10
131104-1055Goggles00
14
15DateProduct Sold
161/5/20231104-1001Television
171/5/20231104-1012Computer
181/6/20231104-1001Television
191/7/20231104-1045Balloon
201/12/20231104-1023Helmet
211/12/20231104-1024Candle
222/14/20231104-1033Shawl
232/2/20231104-1034Fridge
242/3/20231104-1035Magnet
252/4/20231104-1044Chair
262/5/20231104-1044Chair
271/6/20231104-1024Candle
281/5/20231104-1023Helmet
291/6/20231104-1024Candle
302/9/20231104-1035Magnet
312/10/20231104-1033Shawl
Sheet4
Cell Formulas
RangeFormula
E2,G2E2=D2+7
F2F2=E2
E4:E13,G4:G13E4=COUNTIFS($C$16:$C$31,$B4,$A$16:$A$31,">="&D$2,$A$16:$A$31,"<="&E$2)
C16:C31C16=VLOOKUP(B16,$A$4:$B$13,2,FALSE)
 
Upvote 1
Solution
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
ABCDEFG
1Week1Week2
21/01/20238/01/20238/01/202315/01/2023
3Product CodeProductInOutInOut
41104-1001Television20
51104-1012Computer10
61104-1023Helmet11
71104-1024Candle21
81104-1033Shawl00
91104-1034Fridge11
101104-1035Magnet00
111104-1044Chair00
121104-1045Balloon10
131104-1055Goggles00
14
15DateProduct Sold
165/01/20231104-1001Television
175/01/20231104-1012Computer
186/01/20231104-1001Television
197/01/20231104-1045Balloon
2012/01/20231104-1023Helmet
2112/01/20231104-1024Candle
2214/02/20231104-1033Shawl
238/01/20231104-1034Fridge
243/02/20231104-1035Magnet
254/02/20231104-1044Chair
265/02/20231104-1044Chair
276/01/20231104-1024Candle
285/01/20231104-1023Helmet
296/01/20231104-1024Candle
309/02/20231104-1035Magnet
3110/02/20231104-1033Shawl
Count Sold
Cell Formulas
RangeFormula
E2,G2E2=D2+7
F2F2=E2
E4:E13,G4:G13E4=COUNTIFS($C$16:$C$31,$B4,$A$16:$A$31,">="&D$2,$A$16:$A$31,"<="&E$2)
C16:C31C16=VLOOKUP(B16,$A$4:$B$13,2,FALSE)


Wouldn't this slight adjustment be safer?

23 01 30.xlsm
ABCDEFG
1Week1Week2
21/01/20238/01/20238/01/202315/01/2023
3Product CodeProductInOutInOut
41104-1001Television20
51104-1012Computer10
61104-1023Helmet11
71104-1024Candle21
81104-1033Shawl00
91104-1034Fridge10
101104-1035Magnet00
111104-1044Chair00
121104-1045Balloon10
131104-1055Goggles00
14
15DateProduct Sold
165/01/20231104-1001Television
175/01/20231104-1012Computer
186/01/20231104-1001Television
197/01/20231104-1045Balloon
2012/01/20231104-1023Helmet
2112/01/20231104-1024Candle
2214/02/20231104-1033Shawl
238/01/20231104-1034Fridge
243/02/20231104-1035Magnet
254/02/20231104-1044Chair
265/02/20231104-1044Chair
276/01/20231104-1024Candle
285/01/20231104-1023Helmet
296/01/20231104-1024Candle
309/02/20231104-1035Magnet
3110/02/20231104-1033Shawl
Count Sold (2)
Cell Formulas
RangeFormula
E2,G2E2=D2+7
F2F2=E2
E4:E13,G4:G13E4=COUNTIFS($C$16:$C$31,$B4,$A$16:$A$31,">"&E$2-7,$A$16:$A$31,"<="&E$2)
C16:C31C16=VLOOKUP(B16,$A$4:$B$13,2,FALSE)
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top