Max IF formula

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
633
Office Version
  1. 2019
Platform
  1. Windows
Hello,

I am trying to get a Max IF formula to work. At first I thought of using an index match with Max however I read online that the below formula would be a better fit:

=MAX(IF(('% Complete'!A1:A6=A1),'% Complete'!$B$1:$B$7))

I need the formula to return the Maximum percentage from the list below for each color match:

% Complete
A B
Red 50%
Blue10%
Green90%
Yellow50%
Red 100%
Red 75%
Yellow25%

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

This is what the results should be:
A B
Red 100%
Blue10%
Green90%
Yellow50%

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>


However what I get is:

Red 100%
Blue100%
Green100%
Yellow100%

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>


Any help would be greatly appreciated

Thank you :)

Carla
 

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
633
Office Version
  1. 2019
Platform
  1. Windows
Re: Need help with a Max IF formula

Thank you so much! I learned a lot today. I think I will use this formula more often. I like that the AGGREGATE ignores errors and that you can chose to have it return the 1st or 2nd Largest value etc.

Thank you very very much!!

Carla
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Re: Need help with a Max IF formula

Use sumproduct formula:

=SUMPRODUCT(MAX(('Labor Input BLANK'!$F$3:$F$1000=D2)*('Labor Input BLANK'!$H$3:$H$1000)))

Other recommendations. Your current file is 17 megabytes :eek:

Do not copy formulas, or validations, or formats throughout the sheet, just copy what you occupy. If you delete all the rows you don't occupy you will have a 57Kb file. This makes it easier to work. ;)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,165
Office Version
  1. 365
Platform
  1. Windows
Re: Need help with a Max IF formula

You're welcome & thanks for the feedback
 

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
633
Office Version
  1. 2019
Platform
  1. Windows
Re: Need help with a Max IF formula

The worksheet is not mine but I will clean it up before I send it back to my college.

Thank you very much for all your help :)

Carla
 

Watch MrExcel Video

Forum statistics

Threads
1,114,098
Messages
5,545,951
Members
410,713
Latest member
TaremyLunsil
Top