Find the maximum (largest) value in a range of cells and return all values for multiple occurrences/duplicate maximum (largest) values

[Analysis116]

I would like to find the maximum (largest) value in a range of cells and return all values for multiple occurrences/duplicate maximum (largest) values for each user in excel.

Below is a list of the count of trips made by each user ID (user 1, 2, 3, etc. ) to purchase each brand of chocolate.
For example for user 1, he has purchased both Hersheys and Reeses three times (duplicate maximum values). If the Xlookup function is used with the MAX function, then only the brand corresponding to the first MAX value will return, Hersheys, and not Reeses. For user 2 you can see that he has purchased Hersheys, Reeses and Snickers all once. Thus, there are also multiple maximum values in that case also. I would like to return all maximum values if there are multiple occurrences of the maximum value.

Separately, I would also like to return the value for the nth maximum value.

Here is an example data set:

User ID	Hersheys	Reeses	Snickers	Twix	Candy Brand with the Highest Number of Trips for User
1​	3​	3​	0​	0​
2​	1​	1​	1​	0​
3​	4​	4​	1​	1​
4​	0​	0​	1​	0​
5​	2​	1​	2​	0​

 

[Peter_SSs]

I think you have the sentence form nearly correct. We SUM (not COUNT) the cells that have the maximum value, provided there is more than one cell with the maximum value, otherwise return zero.

 

[Analysis116]

I think you have the sentence form nearly correct. We SUM (not COUNT) the cells that have the maximum value, provided there is more than one cell with the maximum value, otherwise return zero.

So what I meant is we count the number of values for the output of MAX. Meaning we count the number of 3s in B2:E:2. If this number is greater than 1 (since we only want to sum trips that have more than one maximum value) then sum the values of the trip in the range B2:E2.

 

[Analysis116]

Actually I might not be understanding it. Would you mind to please explain in sentence form row three where there are multiple 1s? In this case there is more than one max but the max is not greater than one. So how come that row was summed, as it should have been?

 

[Peter_SSs]

In this case there is more than one max but the max is not greater than one.

You never said that the number of trips for the max had to be greater than 1, you said the number of brands had to be greater than 1.

trips where the number of candy brands purchased most often is more than one

In row 3 the number of candy brands purchased most often (Reeses, Snickers, Twix) is 3 and that is certainly greater than one.

In sentence form what my formula does is still as I stated above:

We SUM ... the cells that have the maximum value, provided there is more than one cell with the maximum value, otherwise return zero.

 

[Analysis116]

You never said that the number of trips for the max had to be greater than 1, you said the number of brands had to be greater than 1.

In row 3 the number of candy brands purchased most often (Reeses, Snickers, Twix) is 3 and that is certainly greater than one.

In sentence form what my formula does is still as I stated above:

Thank you very much for taking the time to explain! Much appreciated!

 

[Peter_SSs]

Thank you very much for taking the time to explain! Much appreciated!

No problem. Glad to help.