Sum range of data when dates are between 2 values

LawsH

New Member
Joined
Mar 17, 2023
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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!!
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Can you copy and paste into table? it won't have formulas but it iwll give useful information.
 
Upvote 0
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​
 
Upvote 0
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
 
Upvote 0
Try:
mr excel questions 15.xlsm
ABCDEFGHIJKLM
1DATES MUST MATCH (if you need more specific, tell us)PRODUCT CODE2021-01-012021-04-012021-07-012021-10-012022-01-012022-04-01
2G270270405405270405
3PRODUCTGA135135270270270405
4# Qtrs3U540135135270270540
5Start Qtr2021-04-01D540540540540540540
6Usage:1350S405405135405135540
7C135540270405405135
8H270270135405270405
9Q540135405405405405
10
Sheet11
Cell Formulas
RangeFormula
G1G1=DATE(2021,1,1)
H1:L1H1=EDATE(G1,3)
B5B5=DATE(2021,4,1)
B6B6=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)))
 
Upvote 0
I did the above, not knowing you posted so quickly. I'll do the same with your data in a minute.
 
Upvote 1
How about
Fluff.xlsm
ABCDEFGHIJKLMNO
1Date for Excel (must be 1st of qtr)01/01/202101/04/202101/07/202101/10/202101/01/202201/04/202201/07/202201/10/202201/01/2023
201/07/2021774Product 11211814100
3Product 212019239
4Product 3722029150
5Product 43474327200-148
6Product 53402936170
7Product 62344236230
8Product 75135331102
9Product 814636342302
10Product 93474327200-148
11Product 103402936170
Main
Cell Formulas
RangeFormula
B2B2=SUM(TAKE(DROP(G2:O11,,MATCH(A2,G1:O1,0)-1),,4))
 
Upvote 0
Here is what I posted earlier using your data:
mr excel questions 15.xlsm
ABCDEFGHIJKLMNO
1PRODUCT CODE2021-01-012021-04-012021-07-012021-10-012022-01-012022-04-012022-07-012022-10-012023-01-01
2Product 11211814100000
3PRODUCTProduct 4Product 212019239000
4# Qtrs3Product 3072202915000
5Start Qtr2021-04-01Product 43474327200-148
6Usage:137Product 53402936170000
7Product 62344236230000
8Product 7513533110200
9Product 81463634230200
10Product 934743272000-148
11Product 103402936170000
Sheet11
Cell Formulas
RangeFormula
G1G1=DATE(2021,1,1)
H1:O1H1=EDATE(G1,3)
B5B5=DATE(2021,4,1)
B6B6=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)))
 
Upvote 1
Solution
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!!
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

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