Return max value based on criteria

Steven_TN

New Member
Joined
Jul 20, 2020
Messages
4
Office Version
  1. 2013
Platform
  1. Windows
Hello guys,

I am trying to get the max value based on the supplier's name and their (max) score that I have in a list.

in short: I have a list of suppliers (column A) to which I assigned a colored zone (B) and a score (C). Based on the supplier's name I would like to return the highest score that has been assigned to it, regardless of the zone's color. (I added it to the picture but maybe it wasn't necessary). so for instance, if you look at the picture attached you will see in column D the results I'd like to get. I have highlighted in pink the max values (C) for easy spotting :)

I have tried this formula but it doesn't work: =MAX(IF($B:$B=B2;$C:$C))
Other formulas also don't work for me.

I have excel 2013

thanks for your help!
 

Attachments

  • Cattura.PNG
    Cattura.PNG
    15.8 KB · Views: 4

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,365
Office Version
  1. 365
Platform
  1. Windows
Did you confirm that formula with Ctrl Shift Enter, rather than just Enter?
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,123
maaybe with Power Query

SUPPLIERZONESCORESUPPLIERMax
SAFRANGREEN80SAFRAN80
SAFRANGREEN5IVOPLAST95
SAFRANGREEN5CAREMED91
SAFRANRED10
IVOPLASTYELLOW95
IVOPLASTRED5
CAREMEDRED91
CAREMEDRED45
CAREMEDRED60
CAREMEDRED80
CAREMEDGREEN15

Rich (BB code):
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"SUPPLIER"}, {{"Max", each List.Max([SCORE]), type number}})
in
    Group
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,365
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,421
Messages
5,547,824
Members
410,813
Latest member
Vhinzvirgo
Top