0

I am working on a report that shows different names in column A. I want to compare the values from COL B for each country in column A. In COL C, I would like to return the maximum value from COL B for that country. This is an example of what I'd like:

          COL A     COL B          COL C (OUTPUT RESULT REQUIRED) 
Row 1     USA        $3             -    
Row 2     USA        $7             $7 
Row 3     Italy      $1             $1
Row 4     France     $2             -
Row 5     France     $9             $9
Row 6     France     $4             -

I am using Excel 2016.

How can I do this with a function?

1

Here's a formula that will do the trick.

=IF(COUNTIFS($A$1:$A$6,A1,$B$1:$B$6,">"&B1)=0,B1,"-")

This uses COUNTIFS to count the rows for each country that have a greater value than the current row. Only the max row(s) will return the value.

  • FWIW: there is not detriment to using full column references with COUNIFS(): =IF(COUNTIFS($A:$A,A1,$B:$B,">"&B1)=0,B1,"-") – Scott Craner Mar 29 '17 at 20:09
  • @Excellll: This formula works great. Thanks for all your help. – Nomi Mar 29 '17 at 20:27
  • @Nomi Welcome to Super User! If this answer resolved your problem, please consider accepting the answer by clicking the check mark to the left of the answer. This lets other users know that the solution works and that you have resolved your problem. Thank you! – Excellll Mar 30 '17 at 13:15
0

You can use e.g. this formula:
=IF(AVERAGEIF(A:A,A2,B:B)>B2,"-",B2)

enter image description here

  • Simply beauty. Thanks for all your help. I give you 5 Stars – Nomi Mar 29 '17 at 19:56
  • 1
    Though this will return false positives, change the 4 in France to 8 and it will return 8 as well as the 9. – Scott Craner Mar 29 '17 at 20:01
  • Yes, as I am doing it I realize this thing. What if I just want to display only one hieghest value for each country. – Nomi Mar 29 '17 at 20:03
  • @Nomi you still have not responded to my Edit #2 on Stack Overflow, Also the best formula is the one by Excellll given on this question. – Scott Craner Mar 29 '17 at 20:05
  • @Scott: I just responded – Nomi Mar 29 '17 at 20:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.