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
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 | ||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | |||
| 1 | 01/01/2021 | Year Start Date | Quarters | |||||||||||||||||
| 2 | 10/10/2021 | Today's Date | Quarter 1 | |||||||||||||||||
| 3 | Quarter 2 | |||||||||||||||||||
| 4 | Quarterly / Yearly Sales | Quarter 3 | ||||||||||||||||||
| 5 | Quarter 1 | January-2021 | February-2021 | March-2021 | YTD | Quarter 4 | ||||||||||||||
| 6 | Product | Type | Sold | Profit | Shipping | Sold | Profit | Shipping | Sold | Profit | Shipping | Sold | Profit | Shipping | ||||||
| 7 | Product 1 | Type 1 | 1.00 | $ 10.00 | $ 8.00 | 0.00 | $ - | $ - | 1.00 | $ 10.00 | $ 8.00 | |||||||||
| 8 | Product 1 | Type 2 | 0.00 | $ - | $ - | 0.00 | $ - | $ - | 0.00 | $ - | $ - | |||||||||
| 9 | Product 2 | Type 1 | 0.00 | $ - | $ - | 1.00 | $ 10.00 | $ 8.00 | 0.00 | $ - | $ - | |||||||||
| 10 | Product 2 | Type 2 | 0.00 | $ - | $ - | 1.00 | $ 10.00 | $ 8.00 | 0.00 | $ - | $ - | |||||||||
| 11 | Product 3 | Type 1 | 0.00 | $ - | $ - | 0.00 | $ - | $ - | 1.00 | $ 10.00 | $ 8.00 | |||||||||
| 12 | Product 3 | Type 2 | 0.00 | $ - | $ - | 0.00 | $ - | $ - | 0.00 | $ - | $ - | |||||||||
| 13 | ||||||||||||||||||||
| 14 | All Sales | 2021 Results | 2022 Results | |||||||||||||||||
| 15 | Product | Type | Date | Sales | Profit | Shipping | YTD | YTD | ||||||||||||
| 16 | Product 1 | Type 1 | 01/15/2021 | 1 | $ 10.00 | $ 8.00 | Product | Type | Sold | Profit | Shipping | Sold | Profit | Shipping | ||||||
| 17 | Product 2 | Type 1 | 02/15/2021 | 1 | $ 10.00 | $ 8.00 | Product 1 | Type 1 | 2.00 | $ 20.00 | $ 16.00 | 0.00 | $ - | $ - | ||||||
| 18 | Product 2 | Type 2 | 02/16/2021 | 1 | $ 10.00 | $ 8.00 | Product 1 | Type 2 | 0.00 | $ - | $ - | 0.00 | $ - | $ - | ||||||
| 19 | Product 3 | Type 1 | 03/15/2021 | 1 | $ 10.00 | $ 8.00 | Product 2 | Type 1 | 2.00 | $ 20.00 | $ 16.00 | 0.00 | $ - | $ - | ||||||
| 20 | Product 1 | Type 1 | 03/16/2021 | 1 | $ 10.00 | $ 8.00 | Product 2 | Type 2 | 1.00 | $ 10.00 | $ 8.00 | 0.00 | $ - | $ - | ||||||
| 21 | Product 2 | Type 1 | 04/15/2021 | 1 | $ 10.00 | $ 8.00 | Product 3 | Type 1 | 1.00 | $ 10.00 | $ 8.00 | 1.00 | $ 10.00 | $ 8.00 | ||||||
| 22 | Product 3 | Type 1 | 01/15/2022 | 1 | $ 10.00 | $ 8.00 | Product 3 | Type 2 | 0.00 | $ - | $ - | 0.00 | $ - | $ - | ||||||
Sheet1 | ||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| A2 | A2 | =TODAY() |
| D5 | D5 | =DATE(YEAR(A1),RIGHT(A5)*3-2,1) |
| G5 | G5 | =DATE(YEAR(A1),RIGHT(A5)*3-1,1) |
| J5 | J5 | =DATE(YEAR(A1),RIGHT(A5)*3,1) |
| D7:D12 | D7 | =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:E12 | E7 | =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:F12 | F7 | =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:G12 | G7 | =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:H12 | H7 | =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:I12 | I7 | =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:J12 | J7 | =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:K12 | K7 | =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:L12 | L7 | =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 | ||
|---|---|---|
| Cell | Allow | Criteria |
| A5:C5 | List | =$Q$2:$Q$5 |
