Thread: Find the minimum while omitting some

If I have a column where I want to find the lowest number, but do not want to include some of the data in the same column based on criteria from another column.

Example, I don't want to include the numbers with the letter "N" in column B:

A B
1 N
3 N
2
5
6

=MIN(IF(B1:B5<>"N",A1:A5))

use CTRL-Shift- Enter to enter as an array formula

What if the column is already using a SUM formula?

In my work sheet, column C would contain the "N" and column H would be a SUM calculation.

Attached is an example of what I am trying to do. Looking for a formula that will omit players that are not qualified based on multiple criteria.

Is this a different question/example? I have no idea what you are asking now. It seems to bear no relation to the original post. I'm afraid I can't modify the original formula as I can't relate to the problem.

Where did the footballers come from?

Regards

TMShucks, Your formula resolved the issue for finding the low score and not including the score with the identifier "N".

This is a golf pool sheet I am working on. We have two categories for the winners. A 100% handicap winner and a 80% handicap winner. But, the player has to agree to participate. Not all players will participate in each category (pool).

The "net score" is calculated by subtracting the handicap number, from a gross score. Therefore I have a 100% HDCP column and a 80% HDCP column. The 100% HDCP number will be subtracted from the gross score to determine the winner of the 100% Pool. The 80% HDCP will be subtracted from the gross score to determine the winner of the 80% Pool.

Using a column for each pool (two separate columns, 100% and 80%), I use the number "5" if that player is participating in each pool. Some players play in both, some pick one or the other.

I want to be able to only use those who participate to be included in finding the low net score for each column (100% and 80%). And, not include the guests ("N"). So, I need a formula that recognizes the "5" in column D, participating in column H (100% HDCP), and also a "5" in column E as participating in column J (80% HDCP), as well as not using the guest players from column C with the "N".

Very difficult to explain.

could have done with some in the England team :-(

Are the English footballers now trying to play "Gowf"....

@bobby1949

Your sample data does not match the description of what you want. However, I think I now understand and the formulae below give the results required (both entered as array formulae with CTRL-Shift-Enter):

=MIN(IF($B2:$B11<>"G",IF(C2:C11=5,G2:G11)))

=MIN(IF($B2:$B11<>"G",IF(D2:D11=5,I2:I11)))

The first checks column B for NOT "G", column C for "EQUAL TO 5" and takes the minimum value from column G.

See the sample spreadsheet which I have updated.

Regards

TMShucks, Thank you so very much. Your formula worked well on my sheet.
DonkeyOte, thank you also for your suggestion.

The best part is that I am learning so much from you all here. These sheets are practice for applications I may use in my "real" job.

Now, to be a bigger pest, how would I be able to add the corresponding name to the min number. example: 77 - Steve or 73 - Karl

If I am going too far with this, please, tell to go watch some football.

DdonkeyOte, PERFECT!

I formatted exactly how you suggested and it is fast and clean.

Thanks Much.