min function on non consecutive rows

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have used this function on a golf league score sheet:
=IF(SUMPRODUCT(COUNTIF(INDIRECT({"C7","C11","C15","C19"}),C22))=1,"Winner","No
Winner") to figure out which score for that hole is the lowest and that it is
not duplicated.

Once I expand the cells to check which are not consecutive I cannot go past
30 cells. I have about 50 cells to check in a column. How can I accomplish
this in one formula?
 
Put the cell names that you want to include (C7, C11 etc) in a
separate range, say in F1:F50.

=IF(SUMPRODUCT((C1:C200=C22)*ISNUMBER(MATCH(ADDRESS(ROW(C1:C200),COLUMN(C1:C200),
4),F1:F50,0)))=1,"Winner","No")

Does this help?
Kostis Vezerides
 
=IF(SUMPRODUCT(COUNTIF(INDIRECT("C"&{7,11,15,19,23,27,31,35,39,43,47,51,55,59,63,67,71,75,79,83,87,91,95,99,103,107,11,115,119,123,127,131,135,139,143,137}),C22))=1,"Winner","No
Winner")

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Here's another one:

=IF(SUMPRODUCT(--(MOD(ROW(C7:C200),4)=3),--(C7:C200=C22))=1,"Winner","No
Winner")

Adjust for the real end of range.

Biff
 
Back
Top