retrieve & count text

[JN]

I have a column with text and blank cells. I need a formula that will not
only retrieve the names of the unique text values but also give me a count
how many times they appeared. Example:

Col A
[blank]
Apples
Pears
[blank]
Apples
Oranges

Apples 2
Pears 1
Oranges 1

It could be in 2 different columns...

Thanks in advance!

 

[Bernard Liengme]

Have a look in Help at COUNTIF then come back if you need more.
best wishes

 

[JN]

I can only get that statement to work if I already know the text I'm looking
for. Col A will change constantly and is user defined.

Have a look in Help at COUNTIF then come back if you need more.
best wishes
--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address

I have a column with text and blank cells. I need a formula that will not
only retrieve the names of the unique text values but also give me a count
how many times they appeared. Example:

Col A
[blank]
Apples
Pears
[blank]
Apples
Oranges

Apples 2
Pears 1
Oranges 1

It could be in 2 different columns...

Thanks in advance!

 

[Ron Rosenfeld]

I have a column with text and blank cells. I need a formula that will not
only retrieve the names of the unique text values but also give me a count
how many times they appeared. Example:

Col A
[blank]
Apples
Pears
[blank]
Apples
Oranges

Apples 2
Pears 1
Oranges 1

It could be in 2 different columns...

Thanks in advance!

Although not a formula, you could use a pivot table to list both the unique
entries and their count.

You could also use the advanced filter to list the unique entries, and countif
to get the count.

You could also sort your data, then use the subtotals wizard to get a count of
each item.
--ron

 

[JN]

Absolutely perfect! Thank you Thank you Thank you!

I have a column with text and blank cells. I need a formula that will not
only retrieve the names of the unique text values but also give me a count
how many times they appeared. Example:

Col A
[blank]
Apples
Pears
[blank]
Apples
Oranges

Apples 2
Pears 1
Oranges 1

It could be in 2 different columns...

Thanks in advance!

Although not a formula, you could use a pivot table to list both the unique
entries and their count.

You could also use the advanced filter to list the unique entries, and countif
to get the count.

You could also sort your data, then use the subtotals wizard to get a count of
each item.
--ron

 

[Ron Rosenfeld]

Absolutely perfect! Thank you Thank you Thank you!

You're welcome. Glad to help. Which suggestion did you wind up using?
--ron