# 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!

Tags:

• ✭✭✭✭✭✭
edited 05/20/21

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

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!