Sum range of data when dates are between 2 values

[LawsH]

Hi there. I have a range of product usage data in columns headed by sales quarter starting from January 2021 to date. I need to write a formula that will only add a certain rolling year of data: example I may need to sum April 2021 and then the next 3 quarters. Or start with October 2022 for the next 3 quarters

My product codes are in column F from row 2 onwards
My sales data for 1/1/2021 is in column G row 1
My sales data for 1/4/2021 is in column H row 1 and so on

I can't add the working file using the add in as work doesn't allow.

I have tried sum with match and ifs but can't see to nail this so any advice would be greatly received!!

 

[awoohaw]

Can you copy and paste into table? it won't have formulas but it iwll give useful information.

 

[LawsH]

Date for Excel (must be 1st of qtr)	01/01/2021​	01/04/2021​	01/07/2021​	01/10/2021​	01/01/2022​	01/04/2022​	01/07/2022​	01/10/2022​	01/01/2023​
Product 1	1​	21​	18​	14​	10​	0​
Product 2	1​	20​	19​	23​	9​
Product 3		72​	20​	29​	15​	0​
Product 4	3​	47​	43​	27​	20​	0​		-1​	48​
Product 5	3​	40​	29​	36​	17​	0​
Product 6	2​	34​	42​	36​	23​	0​
Product 7		51​	35​	33​	11​	0​	2​
Product 8	1​	46​	36​	34​	23​	0​	2​
Product 9	3​	47​	43​	27​	20​	0​		-1​	48​
Product 10	3​	40​	29​	36​	17​	0​

 

[LawsH]

Can you copy and paste into table? it won't have formulas but it iwll give useful information.

I hope this helps and thanks for looking
some of the formatting is a little off sorry. What I need to do is if my date selector is 1/4/2021 I need to add all the usage across all products in columns heading 01/04/2021 AND 01/07/2021 AND 01/10/2021 AND 01/01/2022

then my date selector may be 1/1/2022 so I need that column and the next 3 (that's the easier part in my head its always 1 column plus the next 3

 

[awoohaw]

Try:

mr excel questions 15.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	DATES MUST MATCH (if you need more specific, tell us)					PRODUCT CODE	2021-01-01	2021-04-01	2021-07-01	2021-10-01	2022-01-01	2022-04-01
2						G	270	270	405	405	270	405
3	PRODUCT	G				A	135	135	270	270	270	405
4	# Qtrs	3				U	540	135	135	270	270	540
5	Start Qtr	2021-04-01				D	540	540	540	540	540	540
6	Usage:	1350				S	405	405	135	405	135	540
7						C	135	540	270	405	405	135
8						H	270	270	135	405	270	405
9						Q	540	135	405	405	405	405
10
Sheet11

Cell Formulas
Range	Formula
G1	G1	=DATE(2021,1,1)
H1:L1	H1	=EDATE(G1,3)
B5	B5	=DATE(2021,4,1)
B6	B6	=SUM(INDEX($G$2:$L$9,MATCH(B$3,$F$2:$F$9,0),MATCH($B5,$G$1:$L$1,0)): INDEX($G$2:$L$9,MATCH(B$3,$F$2:$F$9,0),3+MATCH($B5,$G$1:$L$1,0)))

 

[awoohaw]

I did the above, not knowing you posted so quickly. I'll do the same with your data in a minute.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1						Date for Excel (must be 1st of qtr)	01/01/2021	01/04/2021	01/07/2021	01/10/2021	01/01/2022	01/04/2022	01/07/2022	01/10/2022	01/01/2023
2	01/07/2021	774				Product 1	1	21	18	14	10	0
3						Product 2	1	20	19	23	9
4						Product 3		72	20	29	15	0
5						Product 4	3	47	43	27	20	0		-1	48
6						Product 5	3	40	29	36	17	0
7						Product 6	2	34	42	36	23	0
8						Product 7		51	35	33	11	0	2
9						Product 8	1	46	36	34	23	0	2
10						Product 9	3	47	43	27	20	0		-1	48
11						Product 10	3	40	29	36	17	0
Main

Cell Formulas
Range	Formula
B2	B2	=SUM(TAKE(DROP(G2:O11,,MATCH(A2,G1:O1,0)-1),,4))

 

[awoohaw]

Here is what I posted earlier using your data:

mr excel questions 15.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1						PRODUCT CODE	2021-01-01	2021-04-01	2021-07-01	2021-10-01	2022-01-01	2022-04-01	2022-07-01	2022-10-01	2023-01-01
2						Product 1	1	21	18	14	10	0	0	0	0
3	PRODUCT	Product 4				Product 2	1	20	19	23	9	0		0	0
4	# Qtrs	3				Product 3	0	72	20	29	15	0		0	0
5	Start Qtr	2021-04-01				Product 4	3	47	43	27	20	0		-1	48
6	Usage:	137				Product 5	3	40	29	36	17	0	0	0	0
7						Product 6	2	34	42	36	23	0	0	0	0
8						Product 7		51	35	33	11	0	2	0	0
9						Product 8	1	46	36	34	23	0	2	0	0
10						Product 9	3	47	43	27	20	0	0	-1	48
11						Product 10	3	40	29	36	17	0	0	0	0
Sheet11

Cell Formulas
Range	Formula
G1	G1	=DATE(2021,1,1)
H1:O1	H1	=EDATE(G1,3)
B5	B5	=DATE(2021,4,1)
B6	B6	=SUM(INDEX($G$2:$O$11,MATCH(B$3,$F$2:$F$11,0),MATCH($B5,$G$1:$L$1,0)): INDEX($G$2:$O$11,MATCH(B$3,$F$2:$F$11,0),3+MATCH($B5,$G$1:$L$1,0)))

 

[LawsH]

Thanks you so much - actually both work for 2 different things I am going to need for my project so thanks for taking the time. I have never used Take and Drop before so will look those up so I understand for the future but my issue (well the current one at least!!) is solved so thanks again!!

 

[Fluff]

Glad we could help & thanks for the feedback.