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!

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!

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!

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!

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!