Unique values in a list that appear at least 3 times (2007)

[What a hoot! The forum automatically removed the word "s-e-x" from my post. I have replace it below with "gender", but the attached spreadsheet is unchanged. Cheers!]

Thanks to everyone who has contributed a suggestion!! I have looked at them all and tested several. Ultimately, for reasons I can't justify, I looked most closely at dadddylonglegs suggestion.

When I adapt his formula for my specific table it works perfectly (as do some others that were suggested). Here is that adaptation:


=SUMPRODUCT(

(Table1[name]<>"")/COUNTIF(Table1[name],Table1[name]&"")

*

(

COUNTIF(Table1[name],Table1[name])>=3

)

)


Okay, the holy grail is now in sight. All I need to do to finish my project is add one more condition!

I still want to count the number of unique names that appear in the name list 3 or more times... but now I want to add the *additional* condition that each unique name (which appears at least 3 times) include one woman!

So.. adjacent to my [name] column is a gender column [gender] populated by the letter "m" or "w".

I tried to adjust the above formula as follows but it didn't work:

=SUMPRODUCT(

(Table1[name]<>"")/COUNTIF(Table1[name],Table1[name]&"")

*

(

AND(

COUNTIF(Table1[name],Table1[name])>=3,

COUNTIF(Table1[gender],"=w")>=1

)

)

)


Perhaps I shouldn't have tried to force this second condition into daddylonglegs formula... perhaps I should have worked more with one of the CSE formulas. After all, I don't mind CSE; in fact, I love it.

So... can anyone guide me from here? I'm attaching a sample spreadsheet if that makes it easier for you.

I would appreciate any and all suggestions,

Cheers,

Jay