michael248363
New Member
- Joined
- Jun 25, 2021
- Messages
- 3
- Office Version
- 2019
- Platform
- Windows
Hopefully, this will make sense, and sorry for the bigger spreadsheet. Columns A-D is a very small subset of my data, but it provides enough each month to illustrate the point. What I need help with is X22:Z38. The correct values are in X2:Z18 but I can't figure out how to add the year to my criteria in X22:X38. I would also like to be able to do this without helper columns E-U as the spreadsheet already goes back a few years and will continue to go forward for years to come. I know I could populate my helper columns so they would be good for the next 10 years and only add another ~3,600 rows without much overhead, but I'd like to know how to do it dynamically from just the table data if possible. FYI, column S only has days from the table as I didn't want to add a couple of hundred rows with no pertinent values. Thanks in advance.
MrExcel.xlsx | |||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | |||
1 | Date | Symbol | Net D | Net P | Week | Month | Day | Year | Month | Net $ | Net % | Year | WW | Net $ | Net % | Date | Net $ | Net % | 1/1/2020 | Time Frame | Net $ | Net % | |||||||
2 | 1/13/2020 | ABCD | -$7.00 | -21% | 3 | 1 | 13 | 2020 | 1/1/2020 | -$20.00 | 9.67% | 2020 | 1 | $0.00 | 0.00% | 1/13/2020 | -$7.00 | -21.00% | Best | August | $32.00 | 38.67% | 2020 | ||||||
3 | 1/27/2020 | KLMN | -$15.00 | -32% | 5 | 1 | 27 | 2020 | 2/1/2020 | -$66.00 | -7.67% | 2020 | 2 | $0.00 | 0.00% | 1/27/2020 | -$13.00 | 25.00% | Months | July | $38.00 | 30.33% | 2020 | ||||||
4 | 1/27/2020 | MNOP | $2.00 | 82% | 5 | 1 | 27 | 2020 | 3/1/2020 | -$6.00 | -16.33% | 2020 | 3 | -$7.00 | -21.00% | 2/3/2020 | -$48.00 | -11.00% | |||||||||||
5 | 2/3/2020 | CDEF | -$23.00 | -6% | 6 | 2 | 3 | 2020 | 4/1/2020 | -$8.00 | 0.67% | 2020 | 4 | $0.00 | 0.00% | 2/25/2020 | -$18.00 | -1.00% | Worst | June | -$19.00 | -19.67% | 2020 | ||||||
6 | 2/3/2020 | EFGH | -$25.00 | -16% | 6 | 2 | 3 | 2020 | 5/1/2020 | -$33.00 | -14.00% | 2020 | 5 | -$13.00 | 25.00% | 3/3/2020 | -$14.00 | -29.50% | Months | December | -$102.00 | -47.67% | 2020 | ||||||
7 | 2/25/2020 | QRST | -$18.00 | -1% | 9 | 2 | 25 | 2020 | 6/1/2020 | -$19.00 | -19.67% | 2020 | 6 | -$48.00 | -11.00% | 3/27/2020 | $8.00 | 10.00% | |||||||||||
8 | 3/3/2020 | GHJK | -$12.00 | -56% | 10 | 3 | 3 | 2020 | 7/1/2020 | $38.00 | 30.33% | 2020 | 9 | -$18.00 | -1.00% | 4/22/2020 | $10.00 | 28.00% | Best | 10/19 - 10/23 | $19.00 | 120.00% | 2020 | ||||||
9 | 3/3/2020 | JKLM | -$2.00 | -3% | 10 | 3 | 3 | 2020 | 8/1/2020 | $32.00 | 38.67% | 2020 | 10 | -$14.00 | -29.50% | 4/23/2020 | -$18.00 | -54.00% | Weeks | 8/3 - 8/7 | $36.00 | 59.50% | 2020 | ||||||
10 | 3/27/2020 | UVWX | $8.00 | 10% | 13 | 3 | 27 | 2020 | 9/1/2020 | $0.00 | 3.67% | 2020 | 11 | $0.00 | 0.00% | 5/4/2020 | -$18.00 | -78.00% | |||||||||||
11 | 4/22/2020 | LMNO | $26.00 | 65% | 17 | 4 | 22 | 2020 | 10/1/2020 | $37.00 | 24.00% | 2020 | 12 | $0.00 | 0.00% | 5/7/2020 | -$15.00 | 18.00% | Worst | 12/21 - 12/25 | -$20.00 | -74.00% | 2020 | ||||||
12 | 4/22/2020 | NOPQ | -$16.00 | -9% | 17 | 4 | 22 | 2020 | 11/1/2020 | $59.00 | 18.67% | 2020 | 13 | $8.00 | 10.00% | 6/17/2020 | -$4.00 | 11.50% | Weeks | 6/22 - 6/26 | -$15.00 | -82.00% | 2020 | ||||||
13 | 4/23/2020 | YZAB | -$18.00 | -54% | 17 | 4 | 23 | 2020 | 12/1/2020 | -$102.00 | -47.67% | 2020 | 14 | $0.00 | 0.00% | 6/22/2020 | -$15.00 | -82.00% | |||||||||||
14 | 5/4/2020 | RSTU | -$18.00 | -78% | 19 | 5 | 4 | 2020 | 1/1/2021 | $19.00 | 87.00% | 2020 | 15 | $0.00 | 0.00% | 7/13/2020 | $33.00 | 39.50% | Best | 10/23/2020 | $19.00 | 120.00% | |||||||
15 | 5/7/2020 | CDEF | -$20.00 | -42% | 19 | 5 | 7 | 2020 | 2/1/2021 | $18.00 | 64.00% | 2020 | 16 | $0.00 | 0.00% | 7/31/2020 | $5.00 | 12.00% | Days | 8/3/2020 | $36.00 | 59.50% | |||||||
16 | 5/7/2020 | LKJH | $5.00 | 78% | 19 | 5 | 7 | 2020 | 3/1/2021 | -$18.00 | -56.00% | 2020 | 17 | -$8.00 | 0.67% | 8/3/2020 | $36.00 | 59.50% | |||||||||||
17 | 6/17/2020 | TUVW | -$11.00 | -25% | 25 | 6 | 17 | 2020 | 4/1/2021 | $18.00 | 81.00% | 2020 | 18 | $0.00 | 0.00% | 8/26/2020 | -$4.00 | -3.00% | Worst | 6/22/2020 | -$18.00 | -78.00% | |||||||
18 | 6/17/2020 | VWXY | $7.00 | 48% | 25 | 6 | 17 | 2020 | 5/1/2021 | -$10.00 | -49.00% | 2020 | 19 | -$33.00 | -14.00% | 9/1/2020 | -$13.00 | -2.00% | Days | 6/22/2021 | -$15.00 | -82.00% | |||||||
19 | 6/22/2020 | GHJK | -$15.00 | -82% | 26 | 6 | 22 | 2020 | 6/1/2021 | -$17.00 | -89.00% | 2020 | 20 | $0.00 | 0.00% | 9/10/2020 | $13.00 | 6.50% | |||||||||||
20 | 7/13/2020 | ZABC | $9.00 | 14% | 29 | 7 | 13 | 2020 | 7/1/2021 | $2.00 | 1.00% | 2020 | 21 | $0.00 | 0.00% | 10/7/2020 | $18.00 | -24.00% | |||||||||||
21 | 7/13/2020 | XYZA | $24.00 | 65% | 29 | 7 | 13 | 2020 | 8/1/2021 | -$15.00 | -7.00% | 2020 | 22 | $0.00 | 0.00% | 10/23/2020 | $19.00 | 120.00% | Time Frame | Net $ | Net % | ||||||||
22 | 7/31/2020 | LMNO | $5.00 | 12% | 31 | 7 | 31 | 2020 | 9/1/2021 | -$12.00 | -41.00% | 2020 | 23 | $0.00 | 0.00% | 11/6/2020 | $24.00 | 8.00% | Best | January | $19.00 | 87.00% | 2021 | ||||||
23 | 8/3/2020 | BCDE | $18.00 | 26% | 32 | 8 | 3 | 2020 | 10/1/2021 | -$7.00 | -2.00% | 2020 | 24 | $0.00 | 0.00% | 11/25/2020 | $35.00 | 24.00% | Months | April | $18.00 | 81.00% | 2021 | ||||||
24 | 8/3/2020 | DEFG | $18.00 | 93% | 32 | 8 | 3 | 2020 | 11/1/2021 | -$17.00 | -33.00% | 2020 | 25 | -$4.00 | 11.50% | 12/3/2020 | -$82.00 | -34.50% | |||||||||||
25 | 8/26/2020 | PQRS | -$4.00 | -3% | 35 | 8 | 26 | 2020 | 12/1/2021 | $2.00 | 15.00% | 2020 | 26 | -$15.00 | -82.00% | 12/21/2020 | -$20.00 | -74.00% | Worst | March | -$18.00 | -56.00% | 2021 | ||||||
26 | 9/1/2020 | HJKL | -$13.00 | -2% | 36 | 9 | 1 | 2020 | 2020 | 27 | $0.00 | 0.00% | 1/14/2021 | $19.00 | 87.00% | Months | June | -$17.00 | -89.00% | 2021 | |||||||||
27 | 9/10/2020 | TUVW | $26.00 | 48% | 37 | 9 | 10 | 2020 | 2020 | 28 | $0.00 | 0.00% | 2/24/2021 | $18.00 | 64.00% | ||||||||||||||
28 | 9/10/2020 | UTSR | -$13.00 | -35% | 37 | 9 | 10 | 2020 | 2020 | 29 | $33.00 | 39.50% | 3/24/2021 | -$18.00 | -56.00% | Best | 10/19 - 10/23 | $19.00 | 120.00% | 2020 | |||||||||
29 | 10/7/2020 | KLMN | $27.00 | 5% | 41 | 10 | 7 | 2020 | 2020 | 30 | $0.00 | 0.00% | 4/23/2021 | $18.00 | 81.00% | Weeks | 1/13 - 1/17 | $19.00 | 87.00% | 2021 | |||||||||
30 | 10/7/2020 | MNOP | -$9.00 | -53% | 41 | 10 | 7 | 2020 | 2020 | 31 | $5.00 | 12.00% | 5/3/2021 | -$10.00 | -49.00% | ||||||||||||||
31 | 10/23/2020 | XZAY | $19.00 | 120% | 43 | 10 | 23 | 2020 | 2020 | 32 | $36.00 | 59.50% | 6/22/2021 | -$17.00 | -89.00% | Worst | 6/22 - 6/26 | -$15.00 | -82.00% | 2020 | |||||||||
32 | 11/6/2020 | QRST | $24.00 | 8% | 45 | 11 | 6 | 2020 | 2020 | 33 | $0.00 | 0.00% | 7/20/2021 | $2.00 | 1.00% | Weeks | 6/22 - 6/26 | -$17.00 | -89.00% | 2021 | |||||||||
33 | 11/25/2020 | BCDE | $6.00 | 38% | 48 | 11 | 25 | 2020 | 2020 | 34 | $0.00 | 0.00% | 8/26/2021 | -$15.00 | -7.00% | ||||||||||||||
34 | 11/25/2020 | MLKJ | $29.00 | 10% | 48 | 11 | 25 | 2020 | 2020 | 35 | -$4.00 | -3.00% | 9/1/2021 | -$12.00 | -41.00% | Best | 10/23/2020 | $19.00 | 120.00% | ||||||||||
35 | 12/3/2020 | STUV | -$56.00 | -21% | 49 | 12 | 3 | 2020 | 2020 | 36 | -$13.00 | -2.00% | 10/20/2021 | -$7.00 | -2.00% | Days | 1/14/2021 | $19.00 | 87.00% | ||||||||||
36 | 12/3/2020 | UVWX | -$26.00 | -48% | 49 | 12 | 3 | 2020 | 2020 | 37 | $13.00 | 6.50% | 11/5/2021 | -$17.00 | -33.00% | ||||||||||||||
37 | 12/21/2020 | FGHJ | -$20.00 | -74% | 52 | 12 | 21 | 2020 | 2020 | 38 | $0.00 | 0.00% | 12/21/2021 | $2.00 | 15.00% | Worst | 6/22/2020 | -$15.00 | -82.00% | ||||||||||
38 | 1/14/2021 | YZAB | $19.00 | 87% | 3 | 1 | 14 | 2021 | 2020 | 39 | $0.00 | 0.00% | Days | 6/22/2021 | -$17.00 | -89.00% | |||||||||||||
39 | 2/24/2021 | OPQR | $18.00 | 64% | 9 | 2 | 24 | 2021 | 2020 | 40 | $0.00 | 0.00% | |||||||||||||||||
40 | 3/24/2021 | STUV | -$18.00 | -56% | 13 | 3 | 24 | 2021 | 2020 | 41 | $18.00 | -24.00% | |||||||||||||||||
41 | 4/23/2021 | PONM | $18.00 | 81% | 17 | 4 | 23 | 2021 | 2020 | 42 | $0.00 | 0.00% | |||||||||||||||||
42 | 5/3/2021 | PQRS | -$10.00 | -49% | 19 | 5 | 3 | 2021 | 2020 | 43 | $19.00 | 120.00% | |||||||||||||||||
43 | 6/22/2021 | GFED | -$17.00 | -89% | 26 | 6 | 22 | 2021 | 2020 | 44 | $0.00 | 0.00% | |||||||||||||||||
44 | 7/20/2021 | CBAZ | $2.00 | 1% | 30 | 7 | 20 | 2021 | 2020 | 45 | $24.00 | 8.00% | |||||||||||||||||
45 | 8/26/2021 | YXWV | -$15.00 | -7% | 35 | 8 | 26 | 2021 | 2020 | 46 | $0.00 | 0.00% | |||||||||||||||||
46 | 9/1/2021 | FGHJ | -$12.00 | -41% | 36 | 9 | 1 | 2021 | 2020 | 47 | $0.00 | 0.00% | |||||||||||||||||
47 | 10/20/2021 | QPON | -$7.00 | -2% | 43 | 10 | 20 | 2021 | 2020 | 48 | $35.00 | 24.00% | |||||||||||||||||
48 | 11/5/2021 | OPQR | -$17.00 | -33% | 45 | 11 | 5 | 2021 | 2020 | 49 | -$82.00 | -34.50% | |||||||||||||||||
49 | 12/21/2021 | HGFE | $2.00 | 15% | 52 | 12 | 21 | 2021 | 2020 | 50 | $0.00 | 0.00% | |||||||||||||||||
50 | 2020 | 51 | $0.00 | 0.00% | |||||||||||||||||||||||||
51 | 2020 | 52 | -$20.00 | -74.00% | |||||||||||||||||||||||||
52 | 2021 | 3 | $19.00 | 87.00% | |||||||||||||||||||||||||
53 | 2021 | 9 | $18.00 | 64.00% | |||||||||||||||||||||||||
54 | 2021 | 13 | -$18.00 | -56.00% | |||||||||||||||||||||||||
55 | 2021 | 17 | $18.00 | 81.00% | |||||||||||||||||||||||||
56 | 2021 | 19 | -$10.00 | -49.00% | |||||||||||||||||||||||||
57 | 2021 | 26 | -$17.00 | -89.00% | |||||||||||||||||||||||||
58 | 2021 | 30 | $2.00 | 1.00% | |||||||||||||||||||||||||
59 | 2021 | 35 | -$15.00 | -7.00% | |||||||||||||||||||||||||
60 | 2021 | 36 | -$12.00 | -41.00% | |||||||||||||||||||||||||
61 | 2021 | 43 | -$7.00 | -2.00% | |||||||||||||||||||||||||
62 | 2021 | 45 | -$17.00 | -33.00% | |||||||||||||||||||||||||
63 | 2021 | 46 | $0.00 | 0.00% | |||||||||||||||||||||||||
64 | 2021 | 47 | $0.00 | 0.00% | |||||||||||||||||||||||||
65 | 2021 | 48 | $0.00 | 0.00% | |||||||||||||||||||||||||
66 | 2021 | 49 | $0.00 | 0.00% | |||||||||||||||||||||||||
67 | 2021 | 50 | $0.00 | 0.00% | |||||||||||||||||||||||||
68 | 2021 | 51 | $0.00 | 0.00% | |||||||||||||||||||||||||
69 | 2021 | 52 | $2.00 | 15.00% | |||||||||||||||||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E2:E49 | E2 | =WEEKNUM(A2) |
F2:F49 | F2 | =MONTH(A2) |
G2:G49 | G2 | =DAY(A2) |
H2:H49 | H2 | =YEAR(A2) |
K2:K25 | K2 | =SUMIFS(Net_D,Date,">="&J2,Date,"<="&EOMONTH(J2,0)) |
L2:L25 | L2 | =AVERAGEIFS(Net_P,Date,">="&J2,Date,"<="&EOMONTH(J2,0)) |
P2:P69 | P2 | =SUMIFS($C$2:$C$49, $E$2:$E$49,"="&O2, $H$2:$H$49,"="&N2) |
Q2:Q69 | Q2 | =IFERROR(AVERAGEIFS($D$2:$D$49, $E$2:$E$49,"="&O2, $H$2:$H$49,"="&N2), 0) |
T2:T37 | T2 | =SUMIF(Date,S2,Net_D) |
U2:U25 | U2 | =AVERAGEIF(Date,S2,Net_P) |
U26:U37 | U26 | =SUMIF(Date,S26,Net_P) |
X22:X23 | X22 | =INDEX($J$2:$J$25,MATCH(LARGE($L$2:$L$25,ROWS($X$22:X22)),$L$2:$L$25,0)) |
Y22:Y23 | Y22 | =INDEX($K$2:$K$25,MATCH(LARGE($L$2:$L$25,ROWS($Y$22:Y22)),$L$2:$L$25,0)) |
Z22:Z23 | Z22 | =INDEX($L$2:$L$25,MATCH(LARGE($L$2:$L$25,ROWS($Z$22:Z22)),$L$2:$L$25,0)) |
X25 | X25 | =INDEX($J$2:$J$25,MATCH(SMALL($L$2:$L$25,ROWS($X25:X$26)),$L$2:$L$25,0)) |
Y25 | Y25 | =INDEX($K$2:$K$25,MATCH(SMALL($L$2:$L$25,ROWS($Y25:Y$26)),$L$2:$L$25,0)) |
Z25 | Z25 | =INDEX($L$2:$L$25,MATCH(SMALL($L$2:$L$25,ROWS($Z25:Z$26)),$L$2:$L$25,0)) |
X26 | X26 | =INDEX($J$2:$J$25,MATCH(SMALL($L$2:$L$25,ROWS($X$26:X26)),$L$2:$L$25,0)) |
Y26 | Y26 | =INDEX($K$2:$K$25,MATCH(SMALL($L$2:$L$25,ROWS($Y$26:Y26)),$L$2:$L$25,0)) |
Z26 | Z26 | =INDEX($L$2:$L$25,MATCH(SMALL($L$2:$L$25,ROWS($Z$26:Z26)),$L$2:$L$25,0)) |
X28:X29 | X28 | =CONCAT(TEXT(DATE(YEAR($W$1),1,1)-WEEKDAY(DATE(YEAR($W$1),1,1),2)+(INDEX($O$2:$O$69,MATCH(LARGE($Q$2:$Q$69,ROWS($X$28:X28)),$Q$2:$Q$69,0))-1)*7+1,"m/d")," - ",(TEXT(DATE(YEAR($W$1),1,INDEX($O$2:$O$69,MATCH(LARGE($Q$2:$Q$69,ROWS($X$28:X28)),$Q$2:$Q$69,0))*7)-WEEKDAY(DATE(YEAR($W$1),1,1)),"m/d"))) |
Y28:Y29 | Y28 | =INDEX($P$2:$P$69,MATCH(LARGE($Q$2:$Q$69,ROWS($Y$28:Y28)),$Q$2:$Q$69,0)) |
Z28:Z29 | Z28 | =INDEX($Q$2:$Q$69,MATCH(LARGE($Q$2:$Q$69,ROWS($Z$28:Z28)),$Q$2:$Q$69,0)) |
X31 | X31 | =CONCAT(TEXT(DATE(YEAR($W$1),1,1)-WEEKDAY(DATE(YEAR($W$1),1,1),2)+(INDEX($O$2:$O$69,MATCH(SMALL($Q$2:$Q$69,ROWS($X31:X$32)),$Q$2:$Q$69,0))-1)*7+1,"m/d")," - ",(TEXT(DATE(YEAR($W$1),1,INDEX($O$2:$O$69,MATCH(SMALL($Q$2:$Q$69,ROWS($X31:X$32)),$Q$2:$Q$69,0))*7)-WEEKDAY(DATE(YEAR($W$1),1,1)),"m/d"))) |
Y31 | Y31 | =INDEX($P$2:$P$69,MATCH(SMALL($Q$2:$Q$69,ROWS($Y31:Y$32)),$Q$2:$Q$69,0)) |
Z31 | Z31 | =INDEX($Q$2:$Q$69,MATCH(SMALL($Q$2:$Q$69,ROWS($Z31:Z$32)),$Q$2:$Q$69,0)) |
X32 | X32 | =CONCAT(TEXT(DATE(YEAR($W$1),1,1)-WEEKDAY(DATE(YEAR($W$1),1,1),2)+(INDEX($O$2:$O$69,MATCH(SMALL($Q$2:$Q$69,ROWS($X$32:X32)),$Q$2:$Q$69,0))-1)*7+1,"m/d")," - ",(TEXT(DATE(YEAR($W$1),1,INDEX($O$2:$O$69,MATCH(SMALL($Q$2:$Q$69,ROWS($X$32:X32)),$Q$2:$Q$69,0))*7)-WEEKDAY(DATE(YEAR($W$1),1,1)),"m/d"))) |
Y32 | Y32 | =INDEX($P$2:$P$69,MATCH(SMALL($Q$2:$Q$69,ROWS($Y$32:Y32)),$Q$2:$Q$69,0)) |
Z32 | Z32 | =INDEX($Q$2:$Q$69,MATCH(SMALL($Q$2:$Q$69,ROWS($Z$32:Z32)),$Q$2:$Q$69,0)) |
X34:X35 | X34 | =INDEX($S$2:$S$103,MATCH(LARGE($U$2:$U$103,ROWS($Z$34:Z34)),$U$2:$U$103,0)) |
Y34:Y35 | Y34 | =INDEX($T$2:$T$103,MATCH(LARGE($U$2:$U$103,ROWS($Z$34:Z34)),$U$2:$U$103,0)) |
Z34:Z35 | Z34 | =INDEX($U$2:$U$103,MATCH(LARGE($U$2:$U$103,ROWS($Z$34:Z34)),$U$2:$U$103,0)) |
X37 | X37 | =INDEX($S$2:$S$103,MATCH(SMALL($U$2:$U$103,ROWS($Z37:Z$38)),$U$2:$U$103,0)) |
Y37 | Y37 | =INDEX($T$2:$T$103,MATCH(SMALL($U$2:$U$103,ROWS($Z37:Z$38)),$U$2:$U$103,0)) |
Z37 | Z37 | =INDEX($U$2:$U$103,MATCH(SMALL($U$2:$U$103,ROWS($Z37:Z$38)),$U$2:$U$103,0)) |
X38 | X38 | =INDEX($S$2:$S$103,MATCH(SMALL($U$2:$U$103,ROWS($Z$38:Z38)),$U$2:$U$103,0)) |
Y38 | Y38 | =INDEX($T$2:$T$103,MATCH(SMALL($U$2:$U$103,ROWS($Z$38:Z38)),$U$2:$U$103,0)) |
Z38 | Z38 | =INDEX($U$2:$U$103,MATCH(SMALL($U$2:$U$103,ROWS($Z$38:Z38)),$U$2:$U$103,0)) |
Named Ranges | ||
---|---|---|
Name | Refers To | Cells |
Date | =Sheet1!$A$2:$A$49 | K2:L25, T2:U37, E2:H2 |
Net_D | =Sheet1!$C$2:$C$49 | K2:K25, T2:T37, P2:P69 |
Net_P | =Sheet1!$D$2:$D$49 | L2:L25, U2:U37, Q2:Q69 |