Need to return the total quantity of a product in the last 3 days

[supergravy]

Have tried searching the forums but can't seem to word my question correctly. Here is a basic table:-

Date	Type	Product	Qty
01/12/2022​	Order	Blue Shirt	1​
02/12/2022​	Order	Blue Shirt	1​
03/12/2022​	Order	Blue Shirt	2​
04/12/2022​	Order	Yellow Shirt	1​
05/12/2022​	Order	Blue Shirt	1​
06/12/2022​	Order	Yellow Shirt	2​
07/12/2022​	Order	Blue Shirt	3​
07/12/2022​	Order	Blue Shirt	1​

On another sheet I want see how many blue shirts have sold in the last 3 days, but am unsure if this can be done with a formula:-

Product	Sold in last few days
Blue Shirt	5
Yellow Shirt	2
Pink Shirt	0

Any helps would be greatly appreciated.

 

[jerry12302]

I would use SUMPRODUCT, but first you need a value to define the last few days.

In my example below, the data range is in D2:G17, including labels, so the first data begins on row 3.

I put the formula for the current date =NOW() in I3 which gives me 12/7/2022.

Next to that cell I input a value that represents the last few days, so for the last three days I input 3 in J3.

Next to that cell in K3 I input the formula =DATE(YEAR(I3-J3+1),MONTH(I3-J3+1),DAY(I3-J3+1)) , which displays 12/5/2022, so my days range to count is Dec 5th through Dec 7th. I tried a simple subtraction I3 - J3 first, which also displayed 12/5/2022, but the results explained below were always off a day, so I changed it to the formula above to be precise.

Next to the first product to count, Blue Shirt for example, in cell J6 I input the following formula, and copied it down through K8.

=SUMPRODUCT(--($F$3:$F$17=I6),--($D$3:$D$17>=$K$3),$G$3:$G$17)

It's basically saying for all the Products in F3:F17 that equal the product input to the left in column I, and for all the dates in D3:D17 that fall in the past 3 days (as identified in J3), in this example 12/5/2022 to 12/7/2022, add up the Qty's in the data range.

Date Type Product Qty 12/7/2022 Order Blue Shirt 1 12/7/2022 3​ 12/5/2022​ 12/6/2022 Order Pink Shirt 3 12/6/2022 Order Blue Shirt 2 Product Sold in last few days 12/6/2022 Order Yellow Shirt 1 Blue Shirt 10​ 12/5/2022 Order Blue Shirt 1 Yellow Shirt 4​ 12/5/2022 Order Yellow Shirt 2 Pink Shirt 5​ 12/5/2022 Order Blue Shirt 3 12/5/2022 Order Pink Shirt 2 12/5/2022 Order Blue Shirt 1 12/5/2022 Order Yellow Shirt 1 12/5/2022 Order Blue Shirt 2 12/3/2022 Order Blue Shirt 3 12/3/2022 Order Blue Shirt 3 12/3/2022 Order Yellow Shirt 1 12/2/2022 Order Blue Shirt 3

 

[lrobbo314]

A way if you have some of the newer dynamic array formulas.

SG.xlsx
	A	B	C	D	E	F	G
1	Date	Type	Product	Qty		Product	Quantity
2	12/1/2022	Order	Blue Shirt	1		Blue Shirt	7
3	12/1/2022	Order	Blue Shirt	1		Yellow Shirt	6
4	12/2/2022	Order	Blue Shirt	2		Pink Shirt	5
5	12/2/2022	Order	Yellow Shirt	1
6	12/3/2022	Order	Blue Shirt	1
7	12/4/2022	Order	Yellow Shirt	2
8	12/4/2022	Order	Blue Shirt	3
9	12/5/2022	Order	Blue Shirt	1
10	12/5/2022	Order	Pink Shirt	2
11	12/6/2022	Order	Pink Shirt	3
12	12/6/2022	Order	Yellow Shirt	4
13	12/7/2022	Order	Blue Shirt	3
Sheet2

Cell Formulas
Range	Formula
F1:G4	F1	=LET(p,UNIQUE(Table1[Product]),s,SUMIFS(Table1[Qty],Table1[Product],p,Table1[Date],">="&TODAY()-3),VSTACK({"Product","Quantity"},HSTACK(p,s)))
A6:A7	A6	=A5+1
Dynamic array formulas.