Most Occurring Value Based on Multiple Criteria

Billy Rock

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!

Leibel S

@Billy Rock

You would need to first calculate it in your 'dump sheet'. An example of this is by adding the following columns:

1. Row ID. System auto number
2. ROW#. Column Formula: =MATCH([Row ID]@row, [Row ID]:[Row ID], 0)
3. 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)
4. 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...

Billy Rock

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.

Leibel S

@Billy Rock

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

Billy Rock

Everything is working except for the index formula on the data consolidation sheet. Getting a #CircularReference

Here are how my sheets look now

Leibel S

@Billy Rock

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 <> "")))

Billy Rock

@Leibel S

What is that last @cell referring to? I think I have every thing else set up correctly.

Leibel S

@Billy Rock

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.

Billy Rock

@Leibel S so do I literally write in "@cell"? I have never known that you could do that within smartsheet!

Billy Rock

@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 <> "")))

Leibel S

@Billy Rock

Did you add all the columns from my original post?

Billy Rock

I believe so. Does that look correct?

Leibel S

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 <> "")))

Billy Rock

That worked! Thank you soooo much