Count function

M

MarekZ

Hi everybody,
I need some help. In worksheet I've column (one) with short strings. I
need to known how much times every of them repeats oneself. Somebody can
tell me is exists function which it will make this.

Thanks
Marek
 
P

Peo Sjoblom

Do you mean count each value? If so one way would be to apply
data>filter>advanced filter, then copy to another location, unique records
only. Then use that list to create the count

if the unique records list starts in H2 going down and the original list is
in A2:A1000

in I2 put

=COUNTIF($A$2:$A$1000,H2)


then copy down
 
B

bj

if the strings are in column A, in B1 enter
=countif(A:A,A1)
copy and paste down,
this will tell you how many times the each string will appear in a cell in
column A

if the string can be a part of a longer string, the answer is more complex,
do you need this?
 
M

MarekZ

Użytkownik bj napisał:
if the strings are in column A, in B1 enter
=countif(A:A,A1)
copy and paste down,
this will tell you how many times the each string will appear in a cell in
column A

if the string can be a part of a longer string, the answer is more complex,
do you need this?

:
Click to expand...
Yes
Some strings looks like:

Account Data
Account information
Account not found
Account Type

Regards
 
B

bj

poentially
=sumproduct(--(not(iserror(find("Account",A1:A1000)))))
will find the number of times a cell has the word "Account" in column A
 
H

Harlan Grove

bj said:
poentially
=sumproduct(--(not(iserror(find("Account",A1:A1000)))))
will find the number of times a cell has the word "Account" in column A
"MarekZ" wrote: ....
Click to expand...

No need for SUMPRODUCT. Use

=COUNTIF(A1:A1000,"Account*")

or if Account were entered in, say, C1,

=COUNTIF(A1:A1000,C1&"*")
 
Top