Hi,

Try this variation

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

untested

Cheers,
Shane Devenshire

"Noel" wrote:

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.

"Noel" wrote:

Mike and Joel --

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

Have a great day.

"Mike H" wrote:

Or without a helper column

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

Mike



"Noel" wrote:

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.