Thread: Count unique entries in one column that meet conditions

Hi Everyone,

[I tried to ask this question yesterday -- but it was a follow-up question stuck at the bottom of a thread. So, with your indulgence, here is a simpler version of the question, complete with an attached spreadsheet, if you wish to use it. I also closed the other thread by marking it "Solved", since it answered my initial question.]


The situation:

I have two columns of data. The data is not in alphabetical order, and every column includes duplicate values.

name gender
jones m
martin f
smith f
collins f
wilson m
jones m
martin f
hughes m
wilson m
martin m
smith f
west f
jones m
west f
martin m


The challenge:

In one cell, count the number of unique names that appear in the name column 3 or more times... with the additional condition that each unique name (which appears at least 3 times) must include at least one one woman!


The correct result:

1. ["Martin" appears 4 times, and includes 2 women.]


Limitations:

For various reasons, I'm really keen to find a stand-alone formula that sits in a single cell. CSE formulas welcome. VB scripts are, unfortunately, not useful to me as I don't have the skills to extend them.


Helpful hints and/or starting points:

The following formulas have been suggested as methods to count the number of unique names that appear at least 3 times. But these formulas *do not address the additional requirement* that each distinct name also include at least one woman.


=SUMPRODUCT((A1:A50<>"")/COUNTIF(A1:A50,A1:A50&"")*(COUNTIF(A1:A50,A1:A50)>=3)) [thanks to: daddylonglegs]

=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)) [thanks to: ron coderre]

=SUM(IF(FREQUENCY(IF(A1:A50<>"",MATCH(A1:A50,A1:A50,0)),ROW(A1:A50)-ROW(A1)+1)>=3,1)) [CSE formula, with thanks to: daddylonglegs]


Well, I've done my best to make this challenge as interesting and efficient as possible for everyone. Unfortunately, I have less than 24 hours to pick an approach. Hopefully, an Excel Forum member will once again ride to my rescue.

With sincerest thanks,

Jay

I doubt you will be able to "simplify" - after all you have had help from the two of the very best around.

Perhaps you could use:

Code:

=SUMPRODUCT(((Table1[name]<>"")/COUNTIF(Table1[name],Table1[name]&""))*(COUNTIF(Table1[name],Table1[name])>=3)*ISNUMBER(MATCH(Table1[name]&":f",Table1[name]&":"&Table1[gender],0)))

DonkeyOte,

The formula that you provided is amazing and astonishing -- best of all, it works!!!!!!!!!!!!!

Thanks to you and daddylonglegs and Ron Coderre and everyone who contributed in stages to the resolution of this challenge.

From my perspective, this was the trickiest question that I have ever posted on this forum... and I never believed that it could possibly be solved as elegantly, let alone without CSE.

Eternal thanks to all -- marked SOLVED!!

Jay

Originally Posted by JayUSA

I never believed that it could possibly be solved as elegantly, let alone without CSE.

I'm obviously glad you have resolved this - all I would say regards the above is don't be duped into thinking that SUMPRODUCT is significantly more efficient than an Array - it is not - there is a negligible difference
(and in which "favour" is debated though general opinion seems to support SUMPRODUCT as being the slightly quicker of the two)

The main "advantage" of SUMPRODUCT over an Array is that is does not require CTRL + SHIFT + ENTER entry making it slightly more robust from an end user perspective... Arrays on the other hand are generally more flexible when it comes to handling underlying error values etc... ie both have their advantages !

Soap Box over.

Originally Posted by JayUSA

#1) The final section of your formula uses the colon symbol in 2different places, but your use of the colon here is new to me. Where can I read about using the colon symbol in this manner? Unfortunately, searching Google for "sumproduct" and "colon" yielded nothing interesting.

the use of the colon is used to concatenate the two columns of interest (name & gender) and to subsequently conduct a test to see if on at least one occasion the combination of "name:f" is found ...
you need not use a colon per se (the character serves no "functional" purpose so to speak), you could use any reasonable delimiter of choice - the main criteria being that it (the delimiter) is not found within either field being concatenated.

Originally Posted by JayUSA

#2) How would your formula change if I wanted not "at least one woman", but "exactly two women"? I tried to fiddle the formula to produce this result, but couldn't get it to work.

Given your use of XL2007 you could replace the ISNUMBER(MATCH with a COUNTIFS test, eg:

Code:

=SUMPRODUCT(((Table1[name]<>"")/COUNTIF(Table1[name],Table1[name]&""))*(COUNTIF(Table1[name],Table1[name])>=3)*(COUNTIFS(Table1[name],Table1[name]&"",Table1[gender],"f")=2))

Originally Posted by JayUSA

#3) Finally, how would your formula change if I wanted not "at least one woman", but "any number of women except for exactly two women"?

Assuming you mean a min. of one and/or greater than two then you could perhaps use:

Code:

=SUMPRODUCT(((Table1[name]<>"")/COUNTIF(Table1[name],Table1[name]&""))*(COUNTIF(Table1[name],Table1[name])>=3)*LOOKUP(COUNTIFS(Table1[name],Table1[name]&"",Table1[gender],"f"),{0,1,2,3},{0,1,0,1}))

In the above the output of the COUNTIFS is compared to a LOOKUP to determine multiplier such that 0 & 2 return multiplier of 0 whereas all other instances return multiplier of 1.

Originally Posted by JayUSA

#4) Lastly, do your answers to the above questions change if the gender is not coded as "m" and "f", but as "0" and "1"? In other words, is there anything intrinsically text-specific in your formulae?

Yes, you would need to replace instances of "f" with 1.