Determing if max value in a range of cells equals any of the other cells in that range

[briguy64]

I am trying to figure out how to determine if there are at least two cells in a range of cells that both have the max value in that range. For example, if A1=13, B1=18, C1=21, and D1=13, the cell with the maximum value is C1=21 and it doesn't match any other value even through A1 and D1 both have a value of 12. Thus, the function should return a "False" response. However, if the example was A1=21, B1=18, C1=21, and D1=13, then the max value is 21 and there are two cells with this value, so the function needs to return a "True".

[protonLeah]

=COUNTIF({range to check},MAX({range to check}))

[briguy64]

Hi Ben,
I tried your suggestion and the function returned a 1 for every case except one in my data set even when there weren't any cells with the same value or when two cells had the same value but they weren't the max value. It did correctly identify the condition when three out of the four cells all had the same max value (returned a value of 3). For the count equation to work correctly for the example A1=13, B1=18, C1=21, and D1=13, it needs to return 0, but for A1=21, B1=18, C1=21, and D1=13, it should return 2. Thanks for getting me one step closer as I think the solution will involve the MAX function somehow.
Brian

[JeteMc]

Try the following modification of Ben's formula: =IF(COUNTIF(A1:D1,MAX(A1:D1))=1,0,COUNTIF(A1:D1,MAX(A1:D1)))
Let us know if you have any questions.

[briguy64]

Thanks JeteMc, your formula worked perfectly!

[JeteMc]

Thank You for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.