Most Occurring Value Based on Multiple Criteria

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

@Leibel S

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