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

[JayUSA]

Hi Everyone,

Thanks to this forum, I've learned a neat CSE formula that calculates the number of unique values in a list, namely:

=COUNT(

1/FREQUENCY(

MATCH($A$1:$A$50,$A$1:$A$50,0)

),

ROW($A$1:$A$50)-ROW(OFFSET($A$1:$A$50,0,0,1,1))

+1))


What I love about this formula is that it is highly efficient -- it's one formula in one cell.

Now, I'm looking to tweak this formula to count the number of unique values that occur at least 3 times.

So, imagine that A1:A50 contain 50 last names from the phone book, 27 of which are unique according to the formula above.

Now, further imagine that only 5 of these unique 27 last names appear at least 3 times.

I'm looking for the formula that returns 5.

And (like always), for simplicity and sharing reasons, I'm hoping to find a single formula that occupies a single cell, and does not rely on additional columns of intermediate information.

Thanks for your input!!

Jay

[daddylonglegs]

Try this formula

=SUM(IF(FREQUENCY(IF(A1:A50<>"",MATCH(A1:A50,A1:A50,0)),ROW(A1:A50)-ROW(A1)+1)>=3,1))

confirmed with CTRL+SHIFT+ENTER

[zbor]

Here, try this:

=SUMPRODUCT(--(COUNTIF($C$1:$C$16,$C$1:$C$16)>=$E$1))/$E$1

Where in E1 you define how much times number can be repeated: from 1 to max

(nope, sorry, not working)

[Ron Coderre]

Not the shortest formula...but, no Ctrl+Shift+Enter required:

=SUMPRODUCT(--($A$2:$A$51<>""),--(COUNTIF($A$2:$A$51,$A$2:$A$51)>=3)
,--(MATCH($A$2:$A$51&"",$A$2:$A$51&"",0)=ROW($A$2:$A$51)-ROW($A$2)+1))

[daddylonglegs]

You could also use this one to avoid CSE......

=SUMPRODUCT((A1:A50<>"")/COUNTIF(A1:A50,A1:A50&"")*(COUNTIF(A1:A50,A1:A50)>=3))

[Ron Coderre]

You could also use this one to avoid CSE......

=SUMPRODUCT((A1:A50<>"")/COUNTIF(A1:A50,A1:A50&"")*(COUNTIF(A1:A50,A1:A50)>=3))

Something about that formula looked awfully familiar...
so I checked my "formula stash" and there it was!.
What's the point in have a stash if I don't even use it? (sheesh)
I'm glad YOU were on the ball! (I sure wasn't)

[JayUSA]

[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