Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 9th 05, 06:41 PM posted to microsoft.public.excel.worksheet.functions
BigH
 
Posts: n/a
Default 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   Report Post  
Old December 9th 05, 08:07 PM posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default 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   Report Post  
Old December 9th 05, 08:09 PM posted to microsoft.public.excel.worksheet.functions
Domenic
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
"COUNTU" function in Excel to count unique entries in a range WayneL Excel Worksheet Functions 20 September 19th 08 03:50 AM
Counting unique records based on date range aspAddict Excel Worksheet Functions 3 October 26th 05 08:12 PM
How do I use a range of numbers in an if statement? pbeattie Excel Worksheet Functions 2 October 10th 05 10:36 PM
Match function...random search? Les Excel Worksheet Functions 10 July 28th 05 11:54 AM
Counting unique text/number cells from a range sudeepd12 Excel Discussion (Misc queries) 3 June 15th 05 07:58 PM


All times are GMT +1. The time now is 01:59 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017