My question is similar to this previous thread: Top summed value list with criteria (INDEX / SORT / SEQUENCE function combination)
I tried modifying this formula to fit my needs but just come up with #NAME? error. I'm working on building this worksheet to teach another coworker to data dump a csv file into a Worksheet named 'TIME' and have 'Sheet 1' reflect a top 10 list of summed times by client (this data set is considerably snipped, typically will run through 750 rows).
I'd like to sum the time spent within the last 7 days per client (client 1, 4, 2 listed multiple times) specifically on *Preparation tasks (omitting Write-Ups or anything else) and return unique values to a Top 10 list (most time spent to least time spent) of client names and the total amount spent.
I modified the formula from the linked post to both =LET(u,UNIQUE(TIME!$B:$B),s,SUMIFS(TIME!$H:$H,TIME!$D:$D,"*Prep*",TIME!$B:$B,u),st,SORT(INDEX(CHOOSE({1,2},u,s),),2,-1),INDEX(st,SEQUENCE(MIN(ROWS(u),10)),{1,2}))
and
=LET(u,UNIQUE(TIME!$B2:$B1000),s,SUMIFS(TIME!$H:$H,TIME!$D:$D,"*Prep*",TIME!$B2:$B1000,u),st,SORT(INDEX(CHOOSE({1,2},u,s),),2,-1),INDEX(st,SEQUENCE(MIN(ROWS(u),10)),{1,2}))
but only get a #NAME? error in return. I also tried replacing "*Prep*" with H1 in my Sheet1 and entered '1120S 2021 Preparation' into H1 to see if it would return anything and still got the error.
Thanks in advance.
I tried modifying this formula to fit my needs but just come up with #NAME? error. I'm working on building this worksheet to teach another coworker to data dump a csv file into a Worksheet named 'TIME' and have 'Sheet 1' reflect a top 10 list of summed times by client (this data set is considerably snipped, typically will run through 750 rows).
I'd like to sum the time spent within the last 7 days per client (client 1, 4, 2 listed multiple times) specifically on *Preparation tasks (omitting Write-Ups or anything else) and return unique values to a Top 10 list (most time spent to least time spent) of client names and the total amount spent.
I modified the formula from the linked post to both =LET(u,UNIQUE(TIME!$B:$B),s,SUMIFS(TIME!$H:$H,TIME!$D:$D,"*Prep*",TIME!$B:$B,u),st,SORT(INDEX(CHOOSE({1,2},u,s),),2,-1),INDEX(st,SEQUENCE(MIN(ROWS(u),10)),{1,2}))
and
=LET(u,UNIQUE(TIME!$B2:$B1000),s,SUMIFS(TIME!$H:$H,TIME!$D:$D,"*Prep*",TIME!$B2:$B1000,u),st,SORT(INDEX(CHOOSE({1,2},u,s),),2,-1),INDEX(st,SEQUENCE(MIN(ROWS(u),10)),{1,2}))
but only get a #NAME? error in return. I also tried replacing "*Prep*" with H1 in my Sheet1 and entered '1120S 2021 Preparation' into H1 to see if it would return anything and still got the error.
Thanks in advance.