min function on non consecutive rows
 

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?

min function on non consecutive rows
 

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(ADDRES S(ROW(C1:C200),COLUMN(C1:C200),
4),F1:F50,0)))=1,"Winner","No")

Does this help?
Kostis Vezerides

On Feb 14, 8:42 pm, bikemrh wrote:
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?

min function on non consecutive rows
 

=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,9 1,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)



"bikemrh" wrote in message
...
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?

min function on non consecutive rows
 

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

"bikemrh" wrote in message
...
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?

min function on non consecutive rows
 

Thanks, worked like a charm. this is what i was looking for.

"Bob Phillips" wrote:

=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,9 1,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)



"bikemrh" wrote in message
...
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?