min function on non consecutive rows

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

bikemrh - 14 Feb 2007 17:42 GMT

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?

Reply to this Message

vezerid - 14 Feb 2007 18:13 GMT

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

> I have used this function on a golf league score sheet:
> =IF(SUMPRODUCT(COUNTIF(INDIRECT({"C7","C11","C15","C19"}),C22))=1,"Winner","No
[quoted text clipped - 4 lines]
> 30 cells. I have about 50 cells to check in a column. How can I accomplish
> this in one formula?

Reply to this Message

Bob Phillips - 14 Feb 2007 18:40 GMT

=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")

Signature

---
HTH

Bob

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

>I have used this function on a golf league score sheet:
> =IF(SUMPRODUCT(COUNTIF(INDIRECT({"C7","C11","C15","C19"}),C22))=1,"Winner","No
[quoted text clipped - 6 lines]
> 30 cells. I have about 50 cells to check in a column. How can I accomplish
> this in one formula?

Reply to this Message

bikemrh - 14 Feb 2007 20:44 GMT

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

> =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")
[quoted text clipped - 9 lines]
> > 30 cells. I have about 50 cells to check in a column. How can I accomplish
> > this in one formula?

Reply to this Message

T. Valko - 14 Feb 2007 19:01 GMT

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

Reply to this Message