Formula Question - please help
 

Hi All,

I need a formula that returns the number of unique times an item appears in
a text list.
Example List:
Pen
Pen
Pencil
Pencil
Stamp

I would a formula that returns 3 for the above (even though there are
multiples I only need to know the number of times something occurs).

I know I can do this with filters but this is not how I want to do it. I
also tried the Sum IF Frequency formula but couldn't seem to get that to
work.

Thanks in advance for your help.

Belle.

Formula Question - please help
 

On Jan 29, 4:33 pm, Belle wrote:
I need a formula that returns the number of unique times
an item appears in a text list.
Example List:
Pen
Pen
Pencil
Pencil
Stamp

I would a formula that returns 3 for the above

If the data are sorted, as in your example, and if you can ensure that
the last row ("Stamp" above) is followed by something else (like an
empty cell), the following might work for you:

=SUMPRODUCT(--(A1:A5<A2:A6))


----- original posting -----

On Jan 29, 4:33*pm, Belle wrote:
Hi All,

I need a formula that returns the number of unique times an item appears in
a text list.
Example List:
Pen
Pen
Pencil
Pencil
Stamp

I would a formula that returns 3 for the above (even though there are
multiples I only need to know the number of times something occurs).

I know I can do this with filters but this is not how I want to do it. I
also tried the Sum IF Frequency *formula but couldn't seem to get that to
work.

Thanks in advance for your help.

Belle.

Formula Question - please help
 

You could try:

=SUMPRODUCT((A1:A5<"")/COUNTIF(A1:A5,A1:A5))

HTH
Elkar


"Belle" wrote:

Hi All,

I need a formula that returns the number of unique times an item appears in
a text list.
Example List:
Pen
Pen
Pencil
Pencil
Stamp

I would a formula that returns 3 for the above (even though there are
multiples I only need to know the number of times something occurs).

I know I can do this with filters but this is not how I want to do it. I
also tried the Sum IF Frequency formula but couldn't seem to get that to
work.

Thanks in advance for your help.

Belle.

Formula Question - please help
 

Try this array* formula on your example:

=SUM(IF(LEN(A1:A10),1/COUNTIF(A1:A10,A1:A10)))

Assumes a data range of A1 to A10 - adjust as necessary.

*An array formula needs to be committed using the key combination of
Ctrl-Shift-Enter (CSE) rather than the usual Enter. If you do this
correctly then Excel will wrap curly braces around that formula when
viewed in the formula bar - do not type these yourself. If you edit/
amend the formula you need to use CSE again.

Hope this helps.

Pete


On Jan 30, 12:33*am, Belle wrote:
Hi All,

I need a formula that returns the number of unique times an item appears in
a text list.
Example List:
Pen
Pen
Pencil
Pencil
Stamp

I would a formula that returns 3 for the above (even though there are
multiples I only need to know the number of times something occurs).

I know I can do this with filters but this is not how I want to do it. I
also tried the Sum IF Frequency *formula but couldn't seem to get that to
work.

Thanks in advance for your help.

Belle.

Formula Question - please help
 

Thanks Pete - this seems to have worked! Thanks a lot!

"Pete_UK" wrote:

Try this array* formula on your example:

=SUM(IF(LEN(A1:A10),1/COUNTIF(A1:A10,A1:A10)))

Assumes a data range of A1 to A10 - adjust as necessary.

*An array formula needs to be committed using the key combination of
Ctrl-Shift-Enter (CSE) rather than the usual Enter. If you do this
correctly then Excel will wrap curly braces around that formula when
viewed in the formula bar - do not type these yourself. If you edit/
amend the formula you need to use CSE again.

Hope this helps.

Pete


On Jan 30, 12:33 am, Belle wrote:
Hi All,

I need a formula that returns the number of unique times an item appears in
a text list.
Example List:
Pen
Pen
Pencil
Pencil
Stamp

I would a formula that returns 3 for the above (even though there are
multiples I only need to know the number of times something occurs).

I know I can do this with filters but this is not how I want to do it. I
also tried the Sum IF Frequency formula but couldn't seem to get that to
work.

Thanks in advance for your help.

Belle.

Formula Question - please help
 

On Thu, 29 Jan 2009 16:33:02 -0800, Belle
wrote:

Hi All,

I need a formula that returns the number of unique times an item appears in
a text list.
Example List:
Pen
Pen
Pencil
Pencil
Stamp

I would a formula that returns 3 for the above (even though there are
multiples I only need to know the number of times something occurs).

I know I can do this with filters but this is not how I want to do it. I
also tried the Sum IF Frequency formula but couldn't seem to get that to
work.

Thanks in advance for your help.

Belle.


If you are counting blanks, try:

=SUMPRODUCT(1/COUNTIF(A1:A5,A1:A5&""))

If you want to ignore blanks, then try:

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

--ron

Formula Question - please help
 

Hi,

You can try using this array formula (Ctrl+Shift+Enter)

=SUM(1/COUNTIF(range,range))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Belle" wrote in message
...
Hi All,

I need a formula that returns the number of unique times an item appears
in
a text list.
Example List:
Pen
Pen
Pencil
Pencil
Stamp

I would a formula that returns 3 for the above (even though there are
multiples I only need to know the number of times something occurs).

I know I can do this with filters but this is not how I want to do it. I
also tried the Sum IF Frequency formula but couldn't seem to get that to
work.

Thanks in advance for your help.

Belle.

Formula Question - please help
 

You're welcome, but many of the other suggestions are very similar.

Pete

On Jan 30, 1:12*am, Belle wrote:
Thanks Pete - this seems to have worked! Thanks a lot!

Formula Question - please help
 

Hi
Try this non array formula
=SUMPRODUCT(1/COUNTIF(A1:A7,A1:A7))

This assume that there're no blank
Cells in the data table

However, if you have a large dataset,
Array formula and this Sumproduct formula
will slow down Excel
Pivot table will be your best solution

HTH

--
Your feedback is very much appreciate, pls click on the Yes button below if
this posting is helpful.

Thank You

cheers, francis









"Belle" wrote:

Hi All,

I need a formula that returns the number of unique times an item appears in
a text list.
Example List:
Pen
Pen
Pencil
Pencil
Stamp

I would a formula that returns 3 for the above (even though there are
multiples I only need to know the number of times something occurs).

I know I can do this with filters but this is not how I want to do it. I
also tried the Sum IF Frequency formula but couldn't seem to get that to
work.

Thanks in advance for your help.

Belle.