2 Large/Small values based on sums of day, week, & month totals

michael248363

New Member
Joined
Jun 25, 2021
Messages
3
Office Version
  1. 2019
Platform
  1. 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
ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1DateSymbolNet DNet PWeekMonthDayYearMonthNet $Net %YearWWNet $Net %DateNet $Net %1/1/2020Time FrameNet $Net %
21/13/2020ABCD-$7.00-21%311320201/1/2020-$20.009.67%20201$0.000.00%1/13/2020-$7.00-21.00%BestAugust$32.0038.67%2020
31/27/2020KLMN-$15.00-32%512720202/1/2020-$66.00-7.67%20202$0.000.00%1/27/2020-$13.0025.00%MonthsJuly$38.0030.33%2020
41/27/2020MNOP$2.0082%512720203/1/2020-$6.00-16.33%20203-$7.00-21.00%2/3/2020-$48.00-11.00%
52/3/2020CDEF-$23.00-6%62320204/1/2020-$8.000.67%20204$0.000.00%2/25/2020-$18.00-1.00%WorstJune-$19.00-19.67%2020
62/3/2020EFGH-$25.00-16%62320205/1/2020-$33.00-14.00%20205-$13.0025.00%3/3/2020-$14.00-29.50%MonthsDecember-$102.00-47.67%2020
72/25/2020QRST-$18.00-1%922520206/1/2020-$19.00-19.67%20206-$48.00-11.00%3/27/2020$8.0010.00%
83/3/2020GHJK-$12.00-56%103320207/1/2020$38.0030.33%20209-$18.00-1.00%4/22/2020$10.0028.00%Best10/19 - 10/23$19.00120.00%2020
93/3/2020JKLM-$2.00-3%103320208/1/2020$32.0038.67%202010-$14.00-29.50%4/23/2020-$18.00-54.00%Weeks8/3 - 8/7$36.0059.50%2020
103/27/2020UVWX$8.0010%1332720209/1/2020$0.003.67%202011$0.000.00%5/4/2020-$18.00-78.00%
114/22/2020LMNO$26.0065%17422202010/1/2020$37.0024.00%202012$0.000.00%5/7/2020-$15.0018.00%Worst12/21 - 12/25-$20.00-74.00%2020
124/22/2020NOPQ-$16.00-9%17422202011/1/2020$59.0018.67%202013$8.0010.00%6/17/2020-$4.0011.50%Weeks6/22 - 6/26-$15.00-82.00%2020
134/23/2020YZAB-$18.00-54%17423202012/1/2020-$102.00-47.67%202014$0.000.00%6/22/2020-$15.00-82.00%
145/4/2020RSTU-$18.00-78%195420201/1/2021$19.0087.00%202015$0.000.00%7/13/2020$33.0039.50%Best10/23/2020$19.00120.00%
155/7/2020CDEF-$20.00-42%195720202/1/2021$18.0064.00%202016$0.000.00%7/31/2020$5.0012.00%Days8/3/2020$36.0059.50%
165/7/2020LKJH$5.0078%195720203/1/2021-$18.00-56.00%202017-$8.000.67%8/3/2020$36.0059.50%
176/17/2020TUVW-$11.00-25%2561720204/1/2021$18.0081.00%202018$0.000.00%8/26/2020-$4.00-3.00%Worst6/22/2020-$18.00-78.00%
186/17/2020VWXY$7.0048%2561720205/1/2021-$10.00-49.00%202019-$33.00-14.00%9/1/2020-$13.00-2.00%Days6/22/2021-$15.00-82.00%
196/22/2020GHJK-$15.00-82%2662220206/1/2021-$17.00-89.00%202020$0.000.00%9/10/2020$13.006.50%
207/13/2020ZABC$9.0014%2971320207/1/2021$2.001.00%202021$0.000.00%10/7/2020$18.00-24.00%
217/13/2020XYZA$24.0065%2971320208/1/2021-$15.00-7.00%202022$0.000.00%10/23/2020$19.00120.00%Time FrameNet $Net %
227/31/2020LMNO$5.0012%3173120209/1/2021-$12.00-41.00%202023$0.000.00%11/6/2020$24.008.00%BestJanuary$19.0087.00%2021
238/3/2020BCDE$18.0026%3283202010/1/2021-$7.00-2.00%202024$0.000.00%11/25/2020$35.0024.00%MonthsApril$18.0081.00%2021
248/3/2020DEFG$18.0093%3283202011/1/2021-$17.00-33.00%202025-$4.0011.50%12/3/2020-$82.00-34.50%
258/26/2020PQRS-$4.00-3%35826202012/1/2021$2.0015.00%202026-$15.00-82.00%12/21/2020-$20.00-74.00%WorstMarch-$18.00-56.00%2021
269/1/2020HJKL-$13.00-2%36912020202027$0.000.00%1/14/2021$19.0087.00%MonthsJune-$17.00-89.00%2021
279/10/2020TUVW$26.0048%379102020202028$0.000.00%2/24/2021$18.0064.00%
289/10/2020UTSR-$13.00-35%379102020202029$33.0039.50%3/24/2021-$18.00-56.00%Best10/19 - 10/23$19.00120.00%2020
2910/7/2020KLMN$27.005%411072020202030$0.000.00%4/23/2021$18.0081.00%Weeks1/13 - 1/17$19.0087.00%2021
3010/7/2020MNOP-$9.00-53%411072020202031$5.0012.00%5/3/2021-$10.00-49.00%
3110/23/2020XZAY$19.00120%4310232020202032$36.0059.50%6/22/2021-$17.00-89.00%Worst6/22 - 6/26-$15.00-82.00%2020
3211/6/2020QRST$24.008%451162020202033$0.000.00%7/20/2021$2.001.00%Weeks6/22 - 6/26-$17.00-89.00%2021
3311/25/2020BCDE$6.0038%4811252020202034$0.000.00%8/26/2021-$15.00-7.00%
3411/25/2020MLKJ$29.0010%4811252020202035-$4.00-3.00%9/1/2021-$12.00-41.00%Best10/23/2020$19.00120.00%
3512/3/2020STUV-$56.00-21%491232020202036-$13.00-2.00%10/20/2021-$7.00-2.00%Days1/14/2021$19.0087.00%
3612/3/2020UVWX-$26.00-48%491232020202037$13.006.50%11/5/2021-$17.00-33.00%
3712/21/2020FGHJ-$20.00-74%5212212020202038$0.000.00%12/21/2021$2.0015.00%Worst6/22/2020-$15.00-82.00%
381/14/2021YZAB$19.0087%31142021202039$0.000.00%Days6/22/2021-$17.00-89.00%
392/24/2021OPQR$18.0064%92242021202040$0.000.00%
403/24/2021STUV-$18.00-56%133242021202041$18.00-24.00%
414/23/2021PONM$18.0081%174232021202042$0.000.00%
425/3/2021PQRS-$10.00-49%19532021202043$19.00120.00%
436/22/2021GFED-$17.00-89%266222021202044$0.000.00%
447/20/2021CBAZ$2.001%307202021202045$24.008.00%
458/26/2021YXWV-$15.00-7%358262021202046$0.000.00%
469/1/2021FGHJ-$12.00-41%36912021202047$0.000.00%
4710/20/2021QPON-$7.00-2%4310202021202048$35.0024.00%
4811/5/2021OPQR-$17.00-33%451152021202049-$82.00-34.50%
4912/21/2021HGFE$2.0015%5212212021202050$0.000.00%
50202051$0.000.00%
51202052-$20.00-74.00%
5220213$19.0087.00%
5320219$18.0064.00%
54202113-$18.00-56.00%
55202117$18.0081.00%
56202119-$10.00-49.00%
57202126-$17.00-89.00%
58202130$2.001.00%
59202135-$15.00-7.00%
60202136-$12.00-41.00%
61202143-$7.00-2.00%
62202145-$17.00-33.00%
63202146$0.000.00%
64202147$0.000.00%
65202148$0.000.00%
66202149$0.000.00%
67202150$0.000.00%
68202151$0.000.00%
69202152$2.0015.00%
Sheet1
Cell Formulas
RangeFormula
E2:E49E2=WEEKNUM(A2)
F2:F49F2=MONTH(A2)
G2:G49G2=DAY(A2)
H2:H49H2=YEAR(A2)
K2:K25K2=SUMIFS(Net_D,Date,">="&J2,Date,"<="&EOMONTH(J2,0))
L2:L25L2=AVERAGEIFS(Net_P,Date,">="&J2,Date,"<="&EOMONTH(J2,0))
P2:P69P2=SUMIFS($C$2:$C$49, $E$2:$E$49,"="&O2, $H$2:$H$49,"="&N2)
Q2:Q69Q2=IFERROR(AVERAGEIFS($D$2:$D$49, $E$2:$E$49,"="&O2, $H$2:$H$49,"="&N2), 0)
T2:T37T2=SUMIF(Date,S2,Net_D)
U2:U25U2=AVERAGEIF(Date,S2,Net_P)
U26:U37U26=SUMIF(Date,S26,Net_P)
X22:X23X22=INDEX($J$2:$J$25,MATCH(LARGE($L$2:$L$25,ROWS($X$22:X22)),$L$2:$L$25,0))
Y22:Y23Y22=INDEX($K$2:$K$25,MATCH(LARGE($L$2:$L$25,ROWS($Y$22:Y22)),$L$2:$L$25,0))
Z22:Z23Z22=INDEX($L$2:$L$25,MATCH(LARGE($L$2:$L$25,ROWS($Z$22:Z22)),$L$2:$L$25,0))
X25X25=INDEX($J$2:$J$25,MATCH(SMALL($L$2:$L$25,ROWS($X25:X$26)),$L$2:$L$25,0))
Y25Y25=INDEX($K$2:$K$25,MATCH(SMALL($L$2:$L$25,ROWS($Y25:Y$26)),$L$2:$L$25,0))
Z25Z25=INDEX($L$2:$L$25,MATCH(SMALL($L$2:$L$25,ROWS($Z25:Z$26)),$L$2:$L$25,0))
X26X26=INDEX($J$2:$J$25,MATCH(SMALL($L$2:$L$25,ROWS($X$26:X26)),$L$2:$L$25,0))
Y26Y26=INDEX($K$2:$K$25,MATCH(SMALL($L$2:$L$25,ROWS($Y$26:Y26)),$L$2:$L$25,0))
Z26Z26=INDEX($L$2:$L$25,MATCH(SMALL($L$2:$L$25,ROWS($Z$26:Z26)),$L$2:$L$25,0))
X28:X29X28=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:Y29Y28=INDEX($P$2:$P$69,MATCH(LARGE($Q$2:$Q$69,ROWS($Y$28:Y28)),$Q$2:$Q$69,0))
Z28:Z29Z28=INDEX($Q$2:$Q$69,MATCH(LARGE($Q$2:$Q$69,ROWS($Z$28:Z28)),$Q$2:$Q$69,0))
X31X31=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")))
Y31Y31=INDEX($P$2:$P$69,MATCH(SMALL($Q$2:$Q$69,ROWS($Y31:Y$32)),$Q$2:$Q$69,0))
Z31Z31=INDEX($Q$2:$Q$69,MATCH(SMALL($Q$2:$Q$69,ROWS($Z31:Z$32)),$Q$2:$Q$69,0))
X32X32=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")))
Y32Y32=INDEX($P$2:$P$69,MATCH(SMALL($Q$2:$Q$69,ROWS($Y$32:Y32)),$Q$2:$Q$69,0))
Z32Z32=INDEX($Q$2:$Q$69,MATCH(SMALL($Q$2:$Q$69,ROWS($Z$32:Z32)),$Q$2:$Q$69,0))
X34:X35X34=INDEX($S$2:$S$103,MATCH(LARGE($U$2:$U$103,ROWS($Z$34:Z34)),$U$2:$U$103,0))
Y34:Y35Y34=INDEX($T$2:$T$103,MATCH(LARGE($U$2:$U$103,ROWS($Z$34:Z34)),$U$2:$U$103,0))
Z34:Z35Z34=INDEX($U$2:$U$103,MATCH(LARGE($U$2:$U$103,ROWS($Z$34:Z34)),$U$2:$U$103,0))
X37X37=INDEX($S$2:$S$103,MATCH(SMALL($U$2:$U$103,ROWS($Z37:Z$38)),$U$2:$U$103,0))
Y37Y37=INDEX($T$2:$T$103,MATCH(SMALL($U$2:$U$103,ROWS($Z37:Z$38)),$U$2:$U$103,0))
Z37Z37=INDEX($U$2:$U$103,MATCH(SMALL($U$2:$U$103,ROWS($Z37:Z$38)),$U$2:$U$103,0))
X38X38=INDEX($S$2:$S$103,MATCH(SMALL($U$2:$U$103,ROWS($Z$38:Z38)),$U$2:$U$103,0))
Y38Y38=INDEX($T$2:$T$103,MATCH(SMALL($U$2:$U$103,ROWS($Z$38:Z38)),$U$2:$U$103,0))
Z38Z38=INDEX($U$2:$U$103,MATCH(SMALL($U$2:$U$103,ROWS($Z$38:Z38)),$U$2:$U$103,0))
Named Ranges
NameRefers ToCells
Date=Sheet1!$A$2:$A$49K2:L25, T2:U37, E2:H2
Net_D=Sheet1!$C$2:$C$49K2:K25, T2:T37, P2:P69
Net_P=Sheet1!$D$2:$D$49L2:L25, U2:U37, Q2:Q69
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Forum statistics

Threads
1,176,138
Messages
5,901,571
Members
434,906
Latest member
Prabhu_Churi

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
Top