Sumifs YTD sales based on year

B

bdenn

New Member
Joined
Feb 3, 2017
Messages
29
Office Version
  1. 365
Platform
  1. Windows
Hello,
I'm looking to sum all of the sales within 2021 from the "All Sales" table into the "Quarterly / Yearly Sales" table M7, based on the date 01/01/2021 date entered in A1.

I was able to get this working with months using EOMONTH, is this possible with years?

If I change A1 to 01/01/2022 then the YTD sale should display Product 3 Type 1 as a sale.

I have created a table which shows the results of 2021 YTD, & 2022 YTD that I'm looking for.
Notice that 2021 YTD should display Product 2 Type 1, 2 times as there is a sale from 04/15/2021.

Thanks,
Bdenn

Quarterly-YTD.xlsx
ABCDEFGHIJKLMNOPQR
101/01/2021Year Start DateQuarters
210/10/2021Today's DateQuarter 1
3Quarter 2
4Quarterly / Yearly SalesQuarter 3
5Quarter 1January-2021February-2021March-2021YTDQuarter 4
6ProductTypeSoldProfitShippingSoldProfitShippingSoldProfitShippingSoldProfitShipping
7Product 1Type 11.00$ 10.00$ 8.000.00$ -$ -1.00$ 10.00$ 8.00
8Product 1Type 20.00$ -$ -0.00$ -$ -0.00$ -$ -
9Product 2Type 10.00$ -$ -1.00$ 10.00$ 8.000.00$ -$ -
10Product 2Type 20.00$ -$ -1.00$ 10.00$ 8.000.00$ -$ -
11Product 3Type 10.00$ -$ -0.00$ -$ -1.00$ 10.00$ 8.00
12Product 3Type 20.00$ -$ -0.00$ -$ -0.00$ -$ -
13
14All Sales2021 Results2022 Results
15ProductTypeDateSalesProfitShippingYTDYTD
16Product 1Type 101/15/20211$ 10.00$ 8.00ProductTypeSoldProfitShippingSoldProfitShipping
17Product 2Type 102/15/20211$ 10.00$ 8.00Product 1Type 12.00$ 20.00$ 16.000.00$ -$ -
18Product 2Type 202/16/20211$ 10.00$ 8.00Product 1Type 20.00$ -$ -0.00$ -$ -
19Product 3Type 103/15/20211$ 10.00$ 8.00Product 2Type 12.00$ 20.00$ 16.000.00$ -$ -
20Product 1Type 103/16/20211$ 10.00$ 8.00Product 2Type 21.00$ 10.00$ 8.000.00$ -$ -
21Product 2Type 104/15/20211$ 10.00$ 8.00Product 3Type 11.00$ 10.00$ 8.001.00$ 10.00$ 8.00
22Product 3Type 101/15/20221$ 10.00$ 8.00Product 3Type 20.00$ -$ -0.00$ -$ -
Sheet1
Cell Formulas
RangeFormula
A2A2=TODAY()
D5D5=DATE(YEAR(A1),RIGHT(A5)*3-2,1)
G5G5=DATE(YEAR(A1),RIGHT(A5)*3-1,1)
J5J5=DATE(YEAR(A1),RIGHT(A5)*3,1)
D7:D12D7=SUMIFS($F$16:$F$22,$A$16:$A$22,$A7,$C$16:$C$22,$C7,$D$16:$D$22,">="&$D$5,$D$16:$D$22,"<="&EOMONTH($D$5,0))
E7:E12E7=SUMIFS($G$16:$G$22,$A$16:$A$22,$A7,$C$16:$C$22,$C7,$D$16:$D$22,">="&$D$5,$D$16:$D$22,"<="&EOMONTH($D$5,0))
F7:F12F7=SUMIFS($H$16:$H$22,$A$16:$A$22,$A7,$C$16:$C$22,$C7,$D$16:$D$22,">="&$D$5,$D$16:$D$22,"<="&EOMONTH($D$5,0))
G7:G12G7=SUMIFS($F$16:$F$22,$A$16:$A$22,$A7,$C$16:$C$22,$C7,$D$16:$D$22,">="&$G$5,$D$16:$D$22,"<="&EOMONTH($G$5,0))
H7:H12H7=SUMIFS($G$16:$G$22,$A$16:$A$22,$A7,$C$16:$C$22,$C7,$D$16:$D$22,">="&$G$5,$D$16:$D$22,"<="&EOMONTH($G$5,0))
I7:I12I7=SUMIFS($H$16:$H$22,$A$16:$A$22,$A7,$C$16:$C$22,$C7,$D$16:$D$22,">="&$G$5,$D$16:$D$22,"<="&EOMONTH($G$5,0))
J7:J12J7=SUMIFS($F$16:$F$22,$A$16:$A$22,$A7,$C$16:$C$22,$C7,$D$16:$D$22,">="&$J$5,$D$16:$D$22,"<="&EOMONTH($J$5,0))
K7:K12K7=SUMIFS($G$16:$G$22,$A$16:$A$22,$A7,$C$16:$C$22,$C7,$D$16:$D$22,">="&$J$5,$D$16:$D$22,"<="&EOMONTH($J$5,0))
L7:L12L7=SUMIFS($H$16:$H$22,$A$16:$A$22,$A7,$C$16:$C$22,$C7,$D$16:$D$22,">="&$J$5,$D$16:$D$22,"<="&EOMONTH($J$5,0))
Cells with Data Validation
CellAllowCriteria
A5:C5List=$Q$2:$Q$5
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sort by date Sort by votes
How about in M7 dragged down & across
Excel Formula: 
=SUM(FILTER(F$16:F$22,(YEAR($D$16:$D$22)=YEAR($A$1))*($A$16:$A$22=$A7)*($C$16:$C$22=$C7),0))
 
Upvote 0
Solution
Thank You for your reply, that worked great.

Quarterly-YTD.xlsx
ABCDEFGHIJKLMNOPQR
101/01/2021Year Start DateQuarters
210/10/2021Today's DateQuarter 1
3Quarter 2
4Quarterly / Yearly SalesQuarter 3
5Quarter 1January-2021February-2021March-2021YTDQuarter 4
6ProductTypeSoldProfitShippingSoldProfitShippingSoldProfitShippingSoldProfitShipping
7Product 1Type 11.00$ 10.00$ 8.000.00$ -$ -1.00$ 10.00$ 8.002.00$ 20.00$ 16.00
8Product 1Type 20.00$ -$ -0.00$ -$ -0.00$ -$ -0.00$ -$ -
9Product 2Type 10.00$ -$ -1.00$ 10.00$ 8.000.00$ -$ -2.00$ 20.00$ 16.00
10Product 2Type 20.00$ -$ -1.00$ 10.00$ 8.000.00$ -$ -1.00$ 10.00$ 8.00
11Product 3Type 10.00$ -$ -0.00$ -$ -1.00$ 10.00$ 8.001.00$ 10.00$ 8.00
12Product 3Type 20.00$ -$ -0.00$ -$ -0.00$ -$ -0.00$ -$ -
13
14All Sales2021 Results2022 Results
15ProductTypeDateSalesProfitShippingYTDYTD
16Product 1Type 101/15/20211$ 10.00$ 8.00ProductTypeSoldProfitShippingSoldProfitShipping
17Product 2Type 102/15/20211$ 10.00$ 8.00Product 1Type 12.00$ 20.00$ 16.000.00$ -$ -
18Product 2Type 202/16/20211$ 10.00$ 8.00Product 1Type 20.00$ -$ -0.00$ -$ -
19Product 3Type 103/15/20211$ 10.00$ 8.00Product 2Type 12.00$ 20.00$ 16.000.00$ -$ -
20Product 1Type 103/16/20211$ 10.00$ 8.00Product 2Type 21.00$ 10.00$ 8.000.00$ -$ -
21Product 2Type 104/15/20211$ 10.00$ 8.00Product 3Type 11.00$ 10.00$ 8.001.00$ 10.00$ 8.00
22Product 3Type 101/15/20221$ 10.00$ 8.00Product 3Type 20.00$ -$ -0.00$ -$ -
Sheet1
Cell Formulas
RangeFormula
A2A2=TODAY()
D5D5=DATE(YEAR(A1),RIGHT(A5)*3-2,1)
G5G5=DATE(YEAR(A1),RIGHT(A5)*3-1,1)
J5J5=DATE(YEAR(A1),RIGHT(A5)*3,1)
D7:D12D7=SUMIFS($F$16:$F$22,$A$16:$A$22,$A7,$C$16:$C$22,$C7,$D$16:$D$22,">="&$D$5,$D$16:$D$22,"<="&EOMONTH($D$5,0))
E7:E12E7=SUMIFS($G$16:$G$22,$A$16:$A$22,$A7,$C$16:$C$22,$C7,$D$16:$D$22,">="&$D$5,$D$16:$D$22,"<="&EOMONTH($D$5,0))
F7:F12F7=SUMIFS($H$16:$H$22,$A$16:$A$22,$A7,$C$16:$C$22,$C7,$D$16:$D$22,">="&$D$5,$D$16:$D$22,"<="&EOMONTH($D$5,0))
G7:G12G7=SUMIFS($F$16:$F$22,$A$16:$A$22,$A7,$C$16:$C$22,$C7,$D$16:$D$22,">="&$G$5,$D$16:$D$22,"<="&EOMONTH($G$5,0))
H7:H12H7=SUMIFS($G$16:$G$22,$A$16:$A$22,$A7,$C$16:$C$22,$C7,$D$16:$D$22,">="&$G$5,$D$16:$D$22,"<="&EOMONTH($G$5,0))
I7:I12I7=SUMIFS($H$16:$H$22,$A$16:$A$22,$A7,$C$16:$C$22,$C7,$D$16:$D$22,">="&$G$5,$D$16:$D$22,"<="&EOMONTH($G$5,0))
J7:J12J7=SUMIFS($F$16:$F$22,$A$16:$A$22,$A7,$C$16:$C$22,$C7,$D$16:$D$22,">="&$J$5,$D$16:$D$22,"<="&EOMONTH($J$5,0))
K7:K12K7=SUMIFS($G$16:$G$22,$A$16:$A$22,$A7,$C$16:$C$22,$C7,$D$16:$D$22,">="&$J$5,$D$16:$D$22,"<="&EOMONTH($J$5,0))
L7:L12L7=SUMIFS($H$16:$H$22,$A$16:$A$22,$A7,$C$16:$C$22,$C7,$D$16:$D$22,">="&$J$5,$D$16:$D$22,"<="&EOMONTH($J$5,0))
M7:O12M7=SUM(FILTER(F$16:F$22,(YEAR($D$16:$D$22)=YEAR($A$1))*($A$16:$A$22=$A7)*($C$16:$C$22=$C7),0))
Cells with Data Validation
CellAllowCriteria
A5:C5List=$Q$2:$Q$5
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Solved
LOGIC/SUM/FILTER Help
2
Replies
14
Views
513
Alex Blakenburg
A
Sean15
  • Question
Help with SUMPRODUCT
Replies
5
Views
309
Anonymous1378
A
SaraWitch
  • Solved
VLOOKUP formula with multiple IF statements
Replies
2
Views
429
SaraWitch
SaraWitch
K
  • Question
AVERAGEIFS / SUMIF with multiple criteria
Replies
3
Views
807
arthurbr
arthurbr
M
  • Question
SUMIFS
Replies
8
Views
172
awoohaw
awoohaw

Forum statistics

Threads
1,214,520
Messages
6,120,011
Members
448,935
Latest member
ijat

Share this page

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