Return Text Value for Max Count with Specific Criteria

Christina117

Hello, I have the columns as shown above with the Employee Names repeated as necessary for the 3 Categories (Timesheet Not Approved, Timesheet Not Completed and Time Off Not Approved) since one employee could be listed for all three categories for the same pay period. I am looking for a formula to return the Employee Name for the most times listed for each of the 3 categories for each pay period. Example: with the example table, I would need it to return Mulan for the most Time Off Not Approved, John Smith for the most Timesheet Not Approved, and Benedict Cumberbatch for the most Timesheet Not Submitted.

*Preferred to have this automatically updated as new names and dates are added as the year progresses.

KPH

Hi @Christina117

I have an idea for you.

I'll walk through it using your data as an example, but with a couple of extra rows for illustration purposes:

Step 1

Add a helper column to your sheet to count the number of times the employee on the row has a row with the category on this row. Ie John Smith Time off not approved is on one row but Mulan is on two rows.

The formula is:

=COUNTIFS(Category:Category, Category@row, [Employee Name]:[Employee Name], [Employee Name]@row)

The counts look like this:

You can make this a column formula and hide it so it doesn't get in the way.

I added another failed timesheet submission for Benedict so you can see what this would look like for a 3rd instance, and an out of order instance.

Step 2

Create a little table for the results. I have done this in the same sheet but suggest you create a separate sheet and use cross sheet formula.

Add a column for each of your categories and a column for the name to be returned. Like this:

The categories must be typed exactly as they are in the main data. I added an extra category to illustrate what would happen if a category had just one person in it.

Step 3

Now we can use an INDEX COLLECT formula to return the name from the employee column where the number in the helper column is the LARGEST number in that column for that category and the category is the one in the result table.

=INDEX(COLLECT([Employee Name]:[Employee Name], [helper1]:[helper1], LARGE(COLLECT([helper1]:[helper1], Category:Category, Categories@row), 1), Category:Category, Categories@row), 1)

The result is this:

This will automatically include any new names that are added.

Note - if you have a tie, the first name in the list will be returned.

KPH

Hi @Christina117

I have an idea for you.

I'll walk through it using your data as an example, but with a couple of extra rows for illustration purposes:

Step 1

Add a helper column to your sheet to count the number of times the employee on the row has a row with the category on this row. Ie John Smith Time off not approved is on one row but Mulan is on two rows.

The formula is:

=COUNTIFS(Category:Category, Category@row, [Employee Name]:[Employee Name], [Employee Name]@row)

The counts look like this:

You can make this a column formula and hide it so it doesn't get in the way.

I added another failed timesheet submission for Benedict so you can see what this would look like for a 3rd instance, and an out of order instance.

Step 2

Create a little table for the results. I have done this in the same sheet but suggest you create a separate sheet and use cross sheet formula.

Add a column for each of your categories and a column for the name to be returned. Like this:

The categories must be typed exactly as they are in the main data. I added an extra category to illustrate what would happen if a category had just one person in it.

Step 3

Now we can use an INDEX COLLECT formula to return the name from the employee column where the number in the helper column is the LARGEST number in that column for that category and the category is the one in the result table.

=INDEX(COLLECT([Employee Name]:[Employee Name], [helper1]:[helper1], LARGE(COLLECT([helper1]:[helper1], Category:Category, Categories@row), 1), Category:Category, Categories@row), 1)

The result is this:

This will automatically include any new names that are added.

Note - if you have a tie, the first name in the list will be returned.

Christina117

Thank you, KPH!

KPH

Glad I could help, @Christina117