Top 10 Summed Value list with multiple criteria

blaix

New Member
Joined
Jan 21, 2022
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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).
TIMESHEET.PNG


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.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
You're getting the #NAME? error because those formulas are using functions that were added in later versions of Excel than yours. You'd need to do something like this:

Book1 (version 1).xlsb
ABCDEFGHIJK
1timecontactbillabletasksubtaskstartenddurationTop 10
212021 prep5ContactTotal
322021 prep2.516.88
43x2.0833.4
512021 prep1.522.82
64xx1.4341.76
732021 prep1.3261.22
86xx1.2271.1
972021 prep1.181
1082010 prep190.7
1192021 prep0.7100.5
1210xxx0.5110.43
13112021 prep0.43120.25
1412021 prep0.38#N/A0
1542021 prep0.33#N/A0
162xxx0.32#N/A0
17122021 prep0.25#N/A0
Sheet5
Cell Formulas
RangeFormula
J3:J17J3=INDEX($B$2:$B$17,MATCH(MAX(IF(COUNTIF($J$2:$J2,$B$2:$B$17)=0,SUMIF($B$2:$B$17,$B$2:$B$17,$H$2:$H$17),-1)),IF(COUNTIF($J$2:$J2,$B$2:$B$17)=0,SUMIF($B$2:$B$17,$B$2:$B$17,$H$2:$H$17),-2),0))
K3:K17K3=SUMIF($B$2:$B$17,J3,$H$2:$H$17)
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Thanks for the clarification on the #NAME? error. This is working great, though is there a way to omit calculating if column D doesn't say " *Prep* "?
 
Upvote 0
Ugh! Sorry, I can't believe I forgot that piece. Normally I'd use SUMIFS instead of SUMIF to handle that condition, but your version of Excel (2007 by your profile) doesn't have that either. I'll have to come up with something more complicated. Give me some time to work on it.
 
Upvote 0
Eric, could you mock it in 365 using SUMIFS? I'm just going to upgrade, I didn't realize there was such a disparity between what I had at the office and what I should be using.
 
Upvote 0
Yes, if you upgrade this would be far easier. Let me know when you do, and I'll come up with something.
 
Upvote 0
Quick work! Try:

Book1 (version 1).xlsb
ABCDEFGHIJK
1timecontactbillabletasksubtaskstartenddurationTop 10
212021 prep5ContactTotal
322021 prep2.516.88
43x2.0822.5
512021 prep1.531.32
64xx1.4371.1
732021 prep1.3281
86xx1.2290.7
972021 prep1.1110.43
1082010 prep140.33
1192021 prep0.7120.25
1210xxx0.5
13112021 prep0.43
1412021 prep0.38
1542021 prep0.33
162xxx0.32
17122021 prep0.25
Sheet5
Cell Formulas
RangeFormula
J3:K11J3=SORT(CHOOSE({1,2},UNIQUE(FILTER(B2:B17,ISNUMBER(SEARCH("prep",D2:D17)))),SUMIFS(H2:H17,B2:B17,UNIQUE(FILTER(B2:B17,ISNUMBER(SEARCH("prep",D2:D17)))),D2:D17,"*prep*")),2,-1)
Dynamic array formulas.


I should be able to get this shorter, maybe someone else will jump in. Also, this doesn't show just the top 10, it shows the entire list. If you want to limit it to at most 10, let me know.
 
Upvote 0
Solution
Works great, thank you! You are the cat's pajamas, Eric.
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,728
Members
448,294
Latest member
jmjmjmjmjmjm

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