Count Unique Values

  • Thread starter Thread starter Richard
  • Start date Start date
R

Richard

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?
 
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
 
Just read your post properly... Disregard last answer from me, it's nonsense.
Will post a better answer shortly
 
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
 
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.
 
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.
 
This works perfectly! Thank you so much!

T. Valko said:
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.
Click to expand...
 
Back
Top