Formula Question - please help

  • Thread starter Thread starter Belle
  • Start date Start date
B

Belle

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.
 
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
Click to expand...

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 -----
 
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
 
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.
Click to expand...


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
 
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
 
Back
Top