| Home |
| Search |
| Today's Posts |
|
|
|
#1
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! |
|
#2
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! |
|
#3
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 | |||
Hybrid Mode
