Hi
Does anyone know how I can count numbers in a list, but only count the one's
that are different, for example, if counting the following:-
12345
12345
23569
12358
22583

I only want to show a total of 4...

Also, can this be applied to text?

thankyou

How many unique items (count) ?
=SUMPRODUCT(1/COUNTIF(A1:A10,A1:A10&""))
If there are blanks in the range they count as an item
=SUMPRODUCT((A1:A10<"")/COUNTIF(A1:A10,A1:A10&""))
blanks are note included in count
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Fiona" wrote in message
...
Hi
Does anyone know how I can count numbers in a list, but only count the
one's
that are different, for example, if counting the following:-
12345
12345
23569
12358
22583

I only want to show a total of 4...

Also, can this be applied to text?

thankyou

Enter this in B1
=COUNTIF(A1:$A$5,A1)
adjust $A$5 to your actual range
Copy it down
Then use
=SUMIF(B1:B5,1) to get the sum

"Fiona" wrote:

Hi
Does anyone know how I can count numbers in a list, but only count the one's
that are different, for example, if counting the following:-
12345
12345
23569
12358
22583

I only want to show a total of 4...

Also, can this be applied to text?

thankyou

=SUMPRODUCT((A1:A1500<"")/COUNTIF(A1:A1500,A1:A1500&""))

--
Gary''s Student - gsnu200828


"Fiona" wrote:

Hi
Does anyone know how I can count numbers in a list, but only count the one's
that are different, for example, if counting the following:-
12345
12345
23569
12358
22583

I only want to show a total of 4...

Also, can this be applied to text?

thankyou

Thanks Bernard, thats exactly what I needed

Hi Bernard
I wondered if you knew if I can add another 2 conditions to this formula.

Would it be possible to count a list as detailed in your formula, but only
if coloumn B relates to a set condition and if the date is between a certain
time.

e.g

Coloumn A Coloumn B Coloumn C
1111 X 20/01/2009
1111 X 19/01/2009
1234 Y 19/01/2009
1258 Z 15/01/2009
1254 Y 14/01/2009

I want to count Coloumn A (as detailed in your formula) if Coloumn B
xontains X and Coloumn C is greter that 16/01/2009 but less that 21/01/2009.

Is that too many formulas?

Thanks

"Bernard Liengme" wrote:

How many unique items (count) ?
=SUMPRODUCT(1/COUNTIF(A1:A10,A1:A10&""))
If there are blanks in the range they count as an item
=SUMPRODUCT((A1:A10<"")/COUNTIF(A1:A10,A1:A10&""))
blanks are note included in count
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Fiona" wrote in message
...
Hi
Does anyone know how I can count numbers in a list, but only count the
one's
that are different, for example, if counting the following:-
12345
12345
23569
12358
22583

I only want to show a total of 4...

Also, can this be applied to text?

thankyou

=SUMPRODUCT(--(B1:B10="X"), --(C1:C10DATE(2009,1,16),--(C1:C10<DATE(2009,1,21)
will count how may entries have X in the B column and a date In column C
that is 16/01/2009 but less that 21/01/2009.
For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.htm


However, this will count every entry that fits the criteria, not just the
unique ones. If this is required and you are prepared to add a 'helper
column', let me know and I will show you how.

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Fiona Yorke-Saville" wrote in
message ...
Hi Bernard
I wondered if you knew if I can add another 2 conditions to this formula.

Would it be possible to count a list as detailed in your formula, but only
if coloumn B relates to a set condition and if the date is between a
certain
time.

e.g

Coloumn A Coloumn B Coloumn C
1111 X 20/01/2009
1111 X 19/01/2009
1234 Y 19/01/2009
1258 Z 15/01/2009
1254 Y 14/01/2009

I want to count Coloumn A (as detailed in your formula) if Coloumn B
xontains X and Coloumn C is greter that 16/01/2009 but less that
21/01/2009.

Is that too many formulas?

Thanks

"Bernard Liengme" wrote:

How many unique items (count) ?
=SUMPRODUCT(1/COUNTIF(A1:A10,A1:A10&""))
If there are blanks in the range they count as an item
=SUMPRODUCT((A1:A10<"")/COUNTIF(A1:A10,A1:A10&""))
blanks are note included in count
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Fiona" wrote in message
...
Hi
Does anyone know how I can count numbers in a list, but only count the
one's
that are different, for example, if counting the following:-
12345
12345
23569
12358
22583

I only want to show a total of 4...

Also, can this be applied to text?

thankyou