max number depending on column left

psychic duck

New Member
Joined
Nov 8, 2010
Messages
20
NFL 51
NBA 67
NFL 43
NHL 35

In this table I want to be able to find the highest number, but only with the text "NFL" to the left of the number.

So it therefore doesn't include 67 which is the highest answer but gives the value 51 which is the highest answer with NFL to the left of it.

I want it in a formula not in filter.
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Maudise

New Member
Joined
Nov 8, 2010
Messages
11
A messy (but workable) way of doing it would be to input a new column and use the formula

=if($A1 = "NFL", "$B1, "") then use MAX(C:C)
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
NFL 51
NBA 67
NFL 43
NHL 35

In this table I want to be able to find the highest number, but only with the text "NFL" to the left of the number.

So it therefore doesn't include 67 which is the highest answer but gives the value 51 which is the highest answer with NFL to the left of it.

I want it in a formula not in filter.

Try...

Control+shift+enter, not just enter:

=MAX(IF($A$2:$A$5="NFL",$B$2:$B$5))
 

bdub

New Member
Joined
Oct 18, 2010
Messages
47
--- Edit ---

Sorry already answered - Adadin's solution will work great!
 
Last edited:

psychic duck

New Member
Joined
Nov 8, 2010
Messages
20
thanks for the help, I didn't know about entering the data with control shirt enter, but it now works
 

Watch MrExcel Video

Forum statistics

Threads
1,123,295
Messages
5,600,780
Members
414,405
Latest member
Zaurb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top