| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count unique numbers in a range with a given criteria
Hi!
How can I count the unique numbers in a range where I have numbers, blank cells and text, everytime a condition is met? It's like this: Source page Supplier Nº Date 28000 01-01-2007 28001 01-01-2007 jtrjkfff 01-01-2007 28000 01-01-2007 28001 02-01-2007 (blank) 02-01-2007 ddfgdr 02-01-2007 28001 02-01-2007 Results page Date Count of Suppliers 01-01-2007 2 02-01-2007 1 .... Thanks a lot! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count unique numbers in a range with a given criteria
With supplier No and date in A2:B9 and result date in D2 enter this
array formula in E2 (ctrl+shift+enter to execute) and fill down: =COUNT(1/FREQUENCY(IF(B$2:B$9=D2,A$2:A$9),A$2:A$9)) On Feb 9, 11:56 am, Nelson wrote: Hi! How can I count the unique numbers in a range where I have numbers, blank cells and text, everytime a condition is met? It's like this: Source page Supplier N� Date 28000 01-01-2007 28001 01-01-2007 jtrjkfff 01-01-2007 28000 01-01-2007 28001 02-01-2007 (blank) 02-01-2007 ddfgdr 02-01-2007 28001 02-01-2007 Results page Date Count of Suppliers 01-01-2007 2 02-01-2007 1 ... Thanks a lot! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count unique numbers in a range with a given criteria
Try this:
With A2:A100 containing supplier numbers, text, or blanks B2:B100 containing dates and D1: (a date to count uniques for....eg 01/01/2007) This ARRAY FORMULA returns the count unique numeric values from Col_A where the date in Col_B equals D1 E1: =COUNT(1/FREQUENCY(IF($B$2:$B$100=D1,IF(ISNUMBER($A$2:$A$10 0),MATCH($A$2:$A$100,$A$2:$A$100,0))),ROW($A$2:$A$ 100)-ROW($A$2)+1)) Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter]. Enter more dates under D1 and copy the E1 formula down Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Nelson" wrote: Hi! How can I count the unique numbers in a range where I have numbers, blank cells and text, everytime a condition is met? It's like this: Source page Supplier Nº Date 28000 01-01-2007 28001 01-01-2007 jtrjkfff 01-01-2007 28000 01-01-2007 28001 02-01-2007 (blank) 02-01-2007 ddfgdr 02-01-2007 28001 02-01-2007 Results page Date Count of Suppliers 01-01-2007 2 02-01-2007 1 ... Thanks a lot! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count unique numbers in a range with a given criteria
More compact than mine, but....it gets tripped somewhere in the blanks and text
Maybe this?: =COUNT(1/FREQUENCY(IF(B$2:B$9=D2*ISNUMBER(A2:A9),A$2:A$9),A $2:A$9)) Does that help? *********** Regards, Ron XL2002, WinXP "Lori" wrote: With supplier No and date in A2:B9 and result date in D2 enter this array formula in E2 (ctrl+shift+enter to execute) and fill down: =COUNT(1/FREQUENCY(IF(B$2:B$9=D2,A$2:A$9),A$2:A$9)) On Feb 9, 11:56 am, Nelson wrote: Hi! How can I count the unique numbers in a range where I have numbers, blank cells and text, everytime a condition is met? It's like this: Source page Supplier Nº Date 28000 01-01-2007 28001 01-01-2007 jtrjkfff 01-01-2007 28000 01-01-2007 28001 02-01-2007 (blank) 02-01-2007 ddfgdr 02-01-2007 28001 02-01-2007 Results page Date Count of Suppliers 01-01-2007 2 02-01-2007 1 ... Thanks a lot! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count unique numbers in a range with a given criteria
Yes you are right, it's the blank cell which evaluates to zero and so
gets counted. In my test I had this as "(blank)" so the problem didn't arise. On Feb 9, 1:03 pm, Ron Coderre wrote: More compact than mine, but....it gets tripped somewhere in the blanks and text Maybe this?: =COUNT(1/FREQUENCY(IF(B$2:B$9=D2*ISNUMBER(A2:A9),A$2:A$9),A $2:A$9)) Does that help? *********** Regards, Ron XL2002, WinXP "Lori" wrote: With supplier No and date in A2:B9 and result date in D2 enter this array formula in E2 (ctrl+shift+enter to execute) and fill down: =COUNT(1/FREQUENCY(IF(B$2:B$9=D2,A$2:A$9),A$2:A$9)) On Feb 9, 11:56 am, Nelson wrote: Hi! How can I count the unique numbers in a range where I have numbers, blank cells and text, everytime a condition is met? It's like this: Source page Supplier N� Date 28000 01-01-2007 28001 01-01-2007 jtrjkfff 01-01-2007 28000 01-01-2007 28001 02-01-2007 (blank) 02-01-2007 ddfgdr 02-01-2007 28001 02-01-2007 Results page Date Count of Suppliers 01-01-2007 2 02-01-2007 1 ... Thanks a lot! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Unique RANDOM NUMBERS within specified range | Excel Worksheet Functions | |||
| Count Unique records based on the Criteria in another colum | Excel Worksheet Functions | |||
| Count Unique records based on the Criteria in another colum | Excel Worksheet Functions | |||
| Count unique items in range | Excel Worksheet Functions | |||
| How do I count data in range A:A that is dependent upon criteria . | Excel Worksheet Functions | |||
Linear Mode
