|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
June 12th, 2009, 04:15 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Unique Values
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? 
|
|
#2
June 12th, 2009, 04:55 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Unique Values
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?
|
|
#3
June 12th, 2009, 05:03 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Unique Values
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?
|
|
#4
June 12th, 2009, 05:15 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Unique Values
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="grani te"),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?
|
|
#5
June 12th, 2009, 05:51 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Unique Values
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="grani te"),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?
|
|
#6
June 12th, 2009, 06:37 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Unique Values
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" wrote in message ... 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="grani te"),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?
|
|
#7
June 12th, 2009, 09:24 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Unique Values
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" wrote in message ... 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="grani te"),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?
|
|
#8
June 12th, 2009, 10:11 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Unique Values
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Richard" wrote in message ... 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" wrote in message ... 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="grani te"),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?
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
