Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Unique Part Numbers In A Range
Hi,
Hopefully someone can help, in column A i have over 1000 part numbers (some of which are duplicated many times) and in column B i have values ranging from 1 to 320 which correspond to column A part numbers. The header in column A is Part Numbers and the header in column B is Days Late. What i want to calculate is how many unique part numbers are greater than 10 days. hope this makes sense regards Big H |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Unique Part Numbers In A Range
Hi!
Try this: Array entered using the key combo of CTRL,SHIFT,ENTER: =SUM(N(FREQUENCY(IF(B1:B100010,MATCH(A1:A1000,A1: A1000,0)),MATCH(A1:A1000,A1:A1000,0))0)) Biff "BigH" wrote in message ... Hi, Hopefully someone can help, in column A i have over 1000 part numbers (some of which are duplicated many times) and in column B i have values ranging from 1 to 320 which correspond to column A part numbers. The header in column A is Part Numbers and the header in column B is Days Late. What i want to calculate is how many unique part numbers are greater than 10 days. hope this makes sense regards Big H |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Unique Part Numbers In A Range
If Column A contains numerical values, try...
=SUM(IF(FREQUENCY(IF(B2:B1010,A2:A10),IF(B2:B101 0,A2:A10))0,1)) If Column A contains alpha-numeric values, try... =SUM(IF(FREQUENCY(IF((A2:A10<"")*(B2:B1010),MATC H(A2:A10,A2:A10,0)),ROW (A2:A10)-ROW(A2)-1)0,1)) Note that both formulas need to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Adjust the range accordingly. Hope this helps! In article , "BigH" wrote: Hi, Hopefully someone can help, in column A i have over 1000 part numbers (some of which are duplicated many times) and in column B i have values ranging from 1 to 320 which correspond to column A part numbers. The header in column A is Part Numbers and the header in column B is Days Late. What i want to calculate is how many unique part numbers are greater than 10 days. hope this makes sense regards Big H |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"COUNTU" function in Excel to count unique entries in a range | Excel Worksheet Functions | |||
Counting unique records based on date range | Excel Worksheet Functions | |||
How do I use a range of numbers in an if statement? | Excel Worksheet Functions | |||
Match function...random search? | Excel Worksheet Functions | |||
Counting unique text/number cells from a range | Excel Discussion (Misc queries) |