Count Unique Items
 

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.

Count Unique Items
 

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)


"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.

Count Unique Items
 

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.

Count Unique Items
 

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.

Count Unique Items
 

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)


--
Biff
Microsoft Excel MVP


"Mike H" wrote in message
...
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.

Count Unique Items
 

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.

Count Unique Items
 

post your amended formula

"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.

Count Unique Items
 

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.

Count Unique Items
 

Excel won't accept that as a formula!

--
Biff
Microsoft Excel MVP


"Cheers," wrote in message
...
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.

Count Unique Items
 

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