Count Unique Items

[Noel]

I want to count the unique items in a list where a related field matches
another criteria. For example,

A B
1 orange bob
2 orange sue
3 orange hank
4 la ted
5 la ted
6 la ted
7 la joe
8 orange bill
9 orange deb
10 orange sue
11 orange bill

I want to count how many unique people there are in Orange.

 

[Joel]

You need to use an auxillary column C.

Put this formula into C1. Notice where the dollar signs ($) are located.

=SUMPRODUCT(--(A$1:A1="orange"),--(B$1:B1=B1))

This formula will put a 1 the first time a person with orange appears and
puut 2 the 2nd time the person appears on the list. Then you only have to
count the number of 1's with this formula

=COUNTIF(C1:C11,1)

 

[Mike H]

Or without a helper column

=SUMPRODUCT((A1:A100="Orange")/COUNTIF(B1:B100,B1:B100&"")*(B1:B100<>""))

Mike

 

[Noel]

Mike and Joel --

Thank you for your quick response! They both worked like a charm.

Have a great day.

 

[T. Valko]

If a name that is associated with "orange" is also associated with another
location you'll get incorrect results. Using the OP's sample data, change
one of the names for "la" to Bob (which is also associated with "orange").

Try this array formula** (does not account for empty cells):

=SUM(--(FREQUENCY(IF(A1:A11="orange",MATCH(B1:B11,B1:B11,0)),ROW(B1:B11)-MIN(ROW(B1:B11))+1)>0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Noel]

The formulas suggested worked great on my test data, but when I extended it
to my real data, I got the wrong result (the answer was less than one). All
of my ranges are okay. Any thoughts? Thanks.

 

[Mike H]

post your amended formula

The formulas suggested worked great on my test data, but when I extended it
to my real data, I got the wrong result (the answer was less than one). All
of my ranges are okay. Any thoughts? Thanks.

 

[Cheers,]

Hi,

Try this variation

=SUMPRODUCT(--(A1:A100="Orange")/--(COUNTIF(B1:B100,B1:B100&""),--(B1:B100<>"")))

untested

Cheers,
Shane Devenshire

 

[T. Valko]

Excel won't accept that as a formula!

 

[Herbert Seidenberg]

Yet another way.
Excel 2007
No formulas or code:
http://www.mediafire.com/file/ngny5cdwmf0/12_03_08a.xlsx