Most Occurring Value Based on Multiple Criteria
Our organization is counting how often we interact with clients weekly and that process is going great. What I want to do is show who contacted the most clients during a particular week. We have a data dump master sheet and a sheet where I consolidate some high level data. With this one I don't even know where to start with a formula to bring back the value I want, so any suggestions help!
On our data dump sheet we have a "week number" column using "Week Number" smartsheet formula. I count that number to count touches for a certain week. And we have a contact list column for each of our employees (there are 9 of us). Contact on far left and Week Number on far right: Screenshot below:
Then we have the Data Consolidation sheet. So based on week number is there a way to count who appeared most during that week and bring that back value for the corresponding week?
Thank you for your help!
Answers
-
You would need to first calculate it in your 'dump sheet'. An example of this is by adding the following columns:
- Row ID. System auto number
- ROW#. Column Formula: =MATCH([Row ID]@row, [Row ID]:[Row ID], 0)
- Employee Weekly Touch Count. Column Formula: =COUNTIFS([PWS Person Who Touched]:[PWS Person Who Touched], [PWS Person Who Touched]@row, [Week Number]:[Week Number], [Week Number]@row)
- Max Weekly Employee. Column Formula: =IF([Employee Weekly Touch Count]@row = MAX(COLLECT([Employee Weekly Touch Count]:[Employee Weekly Touch Count], [Week Number]:[Week Number], [Week Number]@row)), [PWS Person Who Touched]@row, "")
On you data consolidation sheet you would use the below formula. Make sure to change the cross sheet references to match the ones you make.
=INDEX({Max Weekly Employee Range}, MIN(COLLECT({ROW# Range}, {Week Number Range}, [Week]@row, {Max Weekly Employee Range}, @cell <> "")))
Please note:
Being that you are using a formula that only shows the week number you will end up with issues when moving on to multiple years...
-
I definitely see what the first formula is doing. Can you explain the second one? When I put it in the newly created column the cell remains blank. So it is doing something, but more of an explanation could help what it is trying to do.
-
It should be a contact column.
It will not return anything unless it is the highest employee for that week.
I do need to add something. I WILL CHANGE MY INITIAL COMMENT
-
Everything is working except for the index formula on the data consolidation sheet. Getting a #CircularReference
Here are how my sheets look now
-
All the bolded areas of the index formula should be referenced to the corresponding range in your 'dump sheet'
=INDEX({Max Weekly Employee Range}, MIN(COLLECT({ROW# Range}, {Week Number Range}, [Week]@row, {Max Weekly Employee Range}, @cell <> "")))
-
What is that last @cell referring to? I think I have every thing else set up correctly.
-
Are you referring to the '{Max Weekly Employee Range}'. That is column #4 from my original post.
@cell<>"" is the criteria when looking at the range that the cell cannot be blank.
-
@Leibel S so do I literally write in "@cell"? I have never known that you could do that within smartsheet!
-
@Leibel S so now I am getting #INCORRECT ARGUMENT. Everything seems set up well to me
=INDEX({Max Weekly Employee 1}, MIN(COLLECT(Week2:Week53, {Week Number}, Week@row, {Max Weekly Employee 1}, @cell <> "")))
-
Did you add all the columns from my original post?
-
I believe so. Does that look correct?
-
Looks like the only thing you need to fix is the bolded area below. It should be a cross sheet reference to the Row # column you created.
=INDEX({Max Weekly Employee 1}, MIN(COLLECT({ROW# Range}, {Week Number}, [Week]@row, {Max Weekly Employee 1}, @cell <> "")))
-
That worked! Thank you soooo much
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 464 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!