PC Review
Home Forums Reviews Articles Register
Member Login:

PC Review > Forums > Newsgroups > Microsoft Excel > Microsoft Excel Worksheet Functions > Count Unique Values

Reply
Thread Tools Rate Thread

Count Unique Values

 
 
Richard
Guest
Posts: n/a
 
      12th Jun 2009
Is there a formula that will count the number of unique values based on a
crriteria? For example. I have a sheet of part numbers with coresponding
purchase order number and quantity received. For a number of part numbers
there are multiple receipts on a purchase order so the PO number shows up
multiple times. I want to count the number of PO's base on the Part number.
How can I do that?
 
Reply With Quote
 
 
 
 
Sam Wilson
Guest
Posts: n/a
 
      12th Jun 2009
In the cell where you want the result type =SUM(1/COUNTIF(A1:A6,A1:A6))
(replacing A1:A6 with the range where the PO numbers are) and then rather
than pressing Enter to write the formula to the cell press Ctrl+Shift+Enter

Sam

"Richard" wrote:

> Is there a formula that will count the number of unique values based on a
> crriteria? For example. I have a sheet of part numbers with coresponding
> purchase order number and quantity received. For a number of part numbers
> there are multiple receipts on a purchase order so the PO number shows up
> multiple times. I want to count the number of PO's base on the Part number.
> How can I do that?
 
Reply With Quote
 
Sam Wilson
Guest
Posts: n/a
 
      12th Jun 2009
Just read your post properly... Disregard last answer from me, it's nonsense.
Will post a better answer shortly

"Richard" wrote:

> Is there a formula that will count the number of unique values based on a
> crriteria? For example. I have a sheet of part numbers with coresponding
> purchase order number and quantity received. For a number of part numbers
> there are multiple receipts on a purchase order so the PO number shows up
> multiple times. I want to count the number of PO's base on the Part number.
> How can I do that?
 
Reply With Quote
 
Shane Devenshire
Guest
Posts: n/a
 
      12th Jun 2009
Hi,

It would help if you showed us your data but here is the general idea:

=COUNT(1/FREQUENCY(IF((B2:B10="countertops")*(C2:C10="granite"),A2:A10),A2:A10))

Array enter it - press Shift+Ctrl+Enter

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Richard" wrote:

> Is there a formula that will count the number of unique values based on a
> crriteria? For example. I have a sheet of part numbers with coresponding
> purchase order number and quantity received. For a number of part numbers
> there are multiple receipts on a purchase order so the PO number shows up
> multiple times. I want to count the number of PO's base on the Part number.
> How can I do that?
 
Reply With Quote
 
Richard
Guest
Posts: n/a
 
      12th Jun 2009
Here is a sample of the data:

PN PO Status Qty
10382 107392 Invoiced 500.00
10382 107469 Invoiced 500.00
10382 107554 Invoiced 500.00
10382 108432 Invoiced 500.00
10382 108661 Invoiced 500.00
10382 109499 Invoiced 500.00
10382 109542 Invoiced 500.00
10382 110357 Invoiced 500.00
10382 110659 Invoiced 500.00
10382 110952 Invoiced 1,000.00
10383 100729 Invoiced 575.00
10383 100729 Invoiced 575.00
10383 100729 Invoiced 575.00
10383 100874 Invoiced 500.00
10383 101156 Invoiced 500.00
10383 101365 Invoiced 500.00
10383 101722 Invoiced 500.00
10383 101957 Invoiced 1,000.00
10383 102775 Invoiced 500.00
10383 103226 Invoiced 1,000.00
10383 103168 Invoiced 1,000.00
10383 103226 Invoiced 1,000.00
10383 105417 Invoiced 500.00
10383 105781 Invoiced 500.00
10383 105781 Invoiced 500.00
10383 106102 Invoiced 500.00
10383 106282 Invoiced 500.00
10383 106523 Invoiced 500.00
10383 106982 Invoiced 500.00
10383 107479 Invoiced 1,000.00
10383 107946 Invoiced 500.00
10383 108432 Invoiced 500.00
10383 109622 Invoiced 500.00
10383 109747 Invoiced 500.00
10383 110086 Invoiced 500.00
10384 101339 Invoiced 250.00
10384 101339 Invoiced 250.00
10384 101519 Invoiced 250.00
10384 101769 Invoiced 250.00
10384 102045 Invoiced 250.00
10384 102045 Invoiced 250.00
10384 102298 Invoiced 250.00
10384 103043 Invoiced 250.00
10384 103534 Invoiced 250.00
10384 103534 Invoiced 250.00
10384 104290 Invoiced 250.00
10384 104681 Invoiced 250.00
10384 105126 Invoiced 250.00
10384 105358 Invoiced 250.00

I have the part numbers on another sheet and want the number of times a po
was placed for it. That is why I want to count the unique po numbers.

"Shane Devenshire" wrote:

> Hi,
>
> It would help if you showed us your data but here is the general idea:
>
> =COUNT(1/FREQUENCY(IF((B2:B10="countertops")*(C2:C10="granite"),A2:A10),A2:A10))
>
> Array enter it - press Shift+Ctrl+Enter
>
> --
> If this helps, please click the Yes button.
>
> Cheers,
> Shane Devenshire
>
>
> "Richard" wrote:
>
> > Is there a formula that will count the number of unique values based on a
> > crriteria? For example. I have a sheet of part numbers with coresponding
> > purchase order number and quantity received. For a number of part numbers
> > there are multiple receipts on a purchase order so the PO number shows up
> > multiple times. I want to count the number of PO's base on the Part number.
> > How can I do that?
 
Reply With Quote
 
T. Valko
Guest
Posts: n/a
 
      12th Jun 2009
Try this array formula** :

Data in the range A2:B50

D2 = some PN like 10382

=SUM(IF(FREQUENCY(IF(A2:A50=D2,B2:B50),B2:B50),1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Richard" <(E-Mail Removed)> wrote in message
news:6A1A32FD-2C1F-4946-A4DB-(E-Mail Removed)...
> Here is a sample of the data:
>
> PN PO Status Qty
> 10382 107392 Invoiced 500.00
> 10382 107469 Invoiced 500.00
> 10382 107554 Invoiced 500.00
> 10382 108432 Invoiced 500.00
> 10382 108661 Invoiced 500.00
> 10382 109499 Invoiced 500.00
> 10382 109542 Invoiced 500.00
> 10382 110357 Invoiced 500.00
> 10382 110659 Invoiced 500.00
> 10382 110952 Invoiced 1,000.00
> 10383 100729 Invoiced 575.00
> 10383 100729 Invoiced 575.00
> 10383 100729 Invoiced 575.00
> 10383 100874 Invoiced 500.00
> 10383 101156 Invoiced 500.00
> 10383 101365 Invoiced 500.00
> 10383 101722 Invoiced 500.00
> 10383 101957 Invoiced 1,000.00
> 10383 102775 Invoiced 500.00
> 10383 103226 Invoiced 1,000.00
> 10383 103168 Invoiced 1,000.00
> 10383 103226 Invoiced 1,000.00
> 10383 105417 Invoiced 500.00
> 10383 105781 Invoiced 500.00
> 10383 105781 Invoiced 500.00
> 10383 106102 Invoiced 500.00
> 10383 106282 Invoiced 500.00
> 10383 106523 Invoiced 500.00
> 10383 106982 Invoiced 500.00
> 10383 107479 Invoiced 1,000.00
> 10383 107946 Invoiced 500.00
> 10383 108432 Invoiced 500.00
> 10383 109622 Invoiced 500.00
> 10383 109747 Invoiced 500.00
> 10383 110086 Invoiced 500.00
> 10384 101339 Invoiced 250.00
> 10384 101339 Invoiced 250.00
> 10384 101519 Invoiced 250.00
> 10384 101769 Invoiced 250.00
> 10384 102045 Invoiced 250.00
> 10384 102045 Invoiced 250.00
> 10384 102298 Invoiced 250.00
> 10384 103043 Invoiced 250.00
> 10384 103534 Invoiced 250.00
> 10384 103534 Invoiced 250.00
> 10384 104290 Invoiced 250.00
> 10384 104681 Invoiced 250.00
> 10384 105126 Invoiced 250.00
> 10384 105358 Invoiced 250.00
>
> I have the part numbers on another sheet and want the number of times a po
> was placed for it. That is why I want to count the unique po numbers.
>
> "Shane Devenshire" wrote:
>
>> Hi,
>>
>> It would help if you showed us your data but here is the general idea:
>>
>> =COUNT(1/FREQUENCY(IF((B2:B10="countertops")*(C2:C10="granite"),A2:A10),A2:A10))
>>
>> Array enter it - press Shift+Ctrl+Enter
>>
>> --
>> If this helps, please click the Yes button.
>>
>> Cheers,
>> Shane Devenshire
>>
>>
>> "Richard" wrote:
>>
>> > Is there a formula that will count the number of unique values based on
>> > a
>> > crriteria? For example. I have a sheet of part numbers with
>> > coresponding
>> > purchase order number and quantity received. For a number of part
>> > numbers
>> > there are multiple receipts on a purchase order so the PO number shows
>> > up
>> > multiple times. I want to count the number of PO's base on the Part
>> > number.
>> > How can I do that?
 
Reply With Quote
 
Richard
Guest
Posts: n/a
 
      12th Jun 2009
This works perfectly! Thank you so much!

"T. Valko" wrote:

> Try this array formula** :
>
> Data in the range A2:B50
>
> D2 = some PN like 10382
>
> =SUM(IF(FREQUENCY(IF(A2:A50=D2,B2:B50),B2:B50),1))
>
> ** array formulas need to be entered using the key combination of
> CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
> key then hit ENTER.
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "Richard" <(E-Mail Removed)> wrote in message
> news:6A1A32FD-2C1F-4946-A4DB-(E-Mail Removed)...
> > Here is a sample of the data:
> >
> > PN PO Status Qty
> > 10382 107392 Invoiced 500.00
> > 10382 107469 Invoiced 500.00
> > 10382 107554 Invoiced 500.00
> > 10382 108432 Invoiced 500.00
> > 10382 108661 Invoiced 500.00
> > 10382 109499 Invoiced 500.00
> > 10382 109542 Invoiced 500.00
> > 10382 110357 Invoiced 500.00
> > 10382 110659 Invoiced 500.00
> > 10382 110952 Invoiced 1,000.00
> > 10383 100729 Invoiced 575.00
> > 10383 100729 Invoiced 575.00
> > 10383 100729 Invoiced 575.00
> > 10383 100874 Invoiced 500.00
> > 10383 101156 Invoiced 500.00
> > 10383 101365 Invoiced 500.00
> > 10383 101722 Invoiced 500.00
> > 10383 101957 Invoiced 1,000.00
> > 10383 102775 Invoiced 500.00
> > 10383 103226 Invoiced 1,000.00
> > 10383 103168 Invoiced 1,000.00
> > 10383 103226 Invoiced 1,000.00
> > 10383 105417 Invoiced 500.00
> > 10383 105781 Invoiced 500.00
> > 10383 105781 Invoiced 500.00
> > 10383 106102 Invoiced 500.00
> > 10383 106282 Invoiced 500.00
> > 10383 106523 Invoiced 500.00
> > 10383 106982 Invoiced 500.00
> > 10383 107479 Invoiced 1,000.00
> > 10383 107946 Invoiced 500.00
> > 10383 108432 Invoiced 500.00
> > 10383 109622 Invoiced 500.00
> > 10383 109747 Invoiced 500.00
> > 10383 110086 Invoiced 500.00
> > 10384 101339 Invoiced 250.00
> > 10384 101339 Invoiced 250.00
> > 10384 101519 Invoiced 250.00
> > 10384 101769 Invoiced 250.00
> > 10384 102045 Invoiced 250.00
> > 10384 102045 Invoiced 250.00
> > 10384 102298 Invoiced 250.00
> > 10384 103043 Invoiced 250.00
> > 10384 103534 Invoiced 250.00
> > 10384 103534 Invoiced 250.00
> > 10384 104290 Invoiced 250.00
> > 10384 104681 Invoiced 250.00
> > 10384 105126 Invoiced 250.00
> > 10384 105358 Invoiced 250.00
> >
> > I have the part numbers on another sheet and want the number of times a po
> > was placed for it. That is why I want to count the unique po numbers.
> >
> > "Shane Devenshire" wrote:
> >
> >> Hi,
> >>
> >> It would help if you showed us your data but here is the general idea:
> >>
> >> =COUNT(1/FREQUENCY(IF((B2:B10="countertops")*(C2:C10="granite"),A2:A10),A2:A10))
> >>
> >> Array enter it - press Shift+Ctrl+Enter
> >>
> >> --
> >> If this helps, please click the Yes button.
> >>
> >> Cheers,
> >> Shane Devenshire
> >>
> >>
> >> "Richard" wrote:
> >>
> >> > Is there a formula that will count the number of unique values based on
> >> > a
> >> > crriteria? For example. I have a sheet of part numbers with
> >> > coresponding
> >> > purchase order number and quantity received. For a number of part
> >> > numbers
> >> > there are multiple receipts on a purchase order so the PO number shows
> >> > up
> >> > multiple times. I want to count the number of PO's base on the Part
> >> > number.
> >> > How can I do that?
>
>
>
 
Reply With Quote
 
T. Valko
Guest
Posts: n/a
 
      12th Jun 2009
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Richard" <(E-Mail Removed)> wrote in message
news:49A89129-1F4F-4EC3-869F-(E-Mail Removed)...
> This works perfectly! Thank you so much!
>
> "T. Valko" wrote:
>
>> Try this array formula** :
>>
>> Data in the range A2:B50
>>
>> D2 = some PN like 10382
>>
>> =SUM(IF(FREQUENCY(IF(A2:A50=D2,B2:B50),B2:B50),1))
>>
>> ** array formulas need to be entered using the key combination of
>> CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
>> SHIFT
>> key then hit ENTER.
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Richard" <(E-Mail Removed)> wrote in message
>> news:6A1A32FD-2C1F-4946-A4DB-(E-Mail Removed)...
>> > Here is a sample of the data:
>> >
>> > PN PO Status Qty
>> > 10382 107392 Invoiced 500.00
>> > 10382 107469 Invoiced 500.00
>> > 10382 107554 Invoiced 500.00
>> > 10382 108432 Invoiced 500.00
>> > 10382 108661 Invoiced 500.00
>> > 10382 109499 Invoiced 500.00
>> > 10382 109542 Invoiced 500.00
>> > 10382 110357 Invoiced 500.00
>> > 10382 110659 Invoiced 500.00
>> > 10382 110952 Invoiced 1,000.00
>> > 10383 100729 Invoiced 575.00
>> > 10383 100729 Invoiced 575.00
>> > 10383 100729 Invoiced 575.00
>> > 10383 100874 Invoiced 500.00
>> > 10383 101156 Invoiced 500.00
>> > 10383 101365 Invoiced 500.00
>> > 10383 101722 Invoiced 500.00
>> > 10383 101957 Invoiced 1,000.00
>> > 10383 102775 Invoiced 500.00
>> > 10383 103226 Invoiced 1,000.00
>> > 10383 103168 Invoiced 1,000.00
>> > 10383 103226 Invoiced 1,000.00
>> > 10383 105417 Invoiced 500.00
>> > 10383 105781 Invoiced 500.00
>> > 10383 105781 Invoiced 500.00
>> > 10383 106102 Invoiced 500.00
>> > 10383 106282 Invoiced 500.00
>> > 10383 106523 Invoiced 500.00
>> > 10383 106982 Invoiced 500.00
>> > 10383 107479 Invoiced 1,000.00
>> > 10383 107946 Invoiced 500.00
>> > 10383 108432 Invoiced 500.00
>> > 10383 109622 Invoiced 500.00
>> > 10383 109747 Invoiced 500.00
>> > 10383 110086 Invoiced 500.00
>> > 10384 101339 Invoiced 250.00
>> > 10384 101339 Invoiced 250.00
>> > 10384 101519 Invoiced 250.00
>> > 10384 101769 Invoiced 250.00
>> > 10384 102045 Invoiced 250.00
>> > 10384 102045 Invoiced 250.00
>> > 10384 102298 Invoiced 250.00
>> > 10384 103043 Invoiced 250.00
>> > 10384 103534 Invoiced 250.00
>> > 10384 103534 Invoiced 250.00
>> > 10384 104290 Invoiced 250.00
>> > 10384 104681 Invoiced 250.00
>> > 10384 105126 Invoiced 250.00
>> > 10384 105358 Invoiced 250.00
>> >
>> > I have the part numbers on another sheet and want the number of times a
>> > po
>> > was placed for it. That is why I want to count the unique po numbers.
>> >
>> > "Shane Devenshire" wrote:
>> >
>> >> Hi,
>> >>
>> >> It would help if you showed us your data but here is the general idea:
>> >>
>> >> =COUNT(1/FREQUENCY(IF((B2:B10="countertops")*(C2:C10="granite"),A2:A10),A2:A10))
>> >>
>> >> Array enter it - press Shift+Ctrl+Enter
>> >>
>> >> --
>> >> If this helps, please click the Yes button.
>> >>
>> >> Cheers,
>> >> Shane Devenshire
>> >>
>> >>
>> >> "Richard" wrote:
>> >>
>> >> > Is there a formula that will count the number of unique values based
>> >> > on
>> >> > a
>> >> > crriteria? For example. I have a sheet of part numbers with
>> >> > coresponding
>> >> > purchase order number and quantity received. For a number of part
>> >> > numbers
>> >> > there are multiple receipts on a purchase order so the PO number
>> >> > shows
>> >> > up
>> >> > multiple times. I want to count the number of PO's base on the Part
>> >> > number.
>> >> > How can I do that?
>>
>>
>>
 
Reply With Quote
 
 
 
Reply

« Re: convert hours and minutes into days | How do I change the default header date format in Excel 2007? »
Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Sum and count of unique values Piotr Microsoft Excel Misc 4 19th Feb 2011 01:10 PM
Count Unique Values but not Filtered or Hidden Values Lee Microsoft Excel Worksheet Functions 3 2nd Jun 2009 11:18 PM
Count unique values in one column if values in corresponding columnare null? allie357 Microsoft Excel Programming 1 7th Dec 2008 09:37 AM
Count unique values and create list based on these values =?Utf-8?B?dmlwYTIwMDA=?= Microsoft Excel Worksheet Functions 7 5th Aug 2005 01:17 AM
Count unique values Bmcosta Microsoft Excel Worksheet Functions 7 15th Jun 2004 09:31 PM


Features
Forums Startup Database Reviews Articles FAQ / Guidelines About Us
 

Advertising
 

Newsgroups
Windows Vista Windows XP Microsoft Word Microsoft DotNet Windows 2000 AntiSpyware Microsoft Access Microsoft Excel Microsoft Outlook Hardware
 


All times are GMT +1. The time now is 04:37 PM.
Powered by vBulletin®. Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2010, Crawlability, Inc.
Contact Us - Archive - Privacy Statement - Top