Count function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MarekZ - 23 Jul 2007 16:05 GMT

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

Reply to this Message

Peo Sjoblom - 23 Jul 2007 16:13 GMT

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

Signature

Regards,

Peo Sjoblom

> Hi everybody,
> I need some help. In worksheet I've column (one) with short strings. I
[quoted text clipped - 3 lines]
> Thanks
> Marek

Reply to this Message

bj - 23 Jul 2007 16:14 GMT

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?

> Hi everybody,
> I need some help. In worksheet I've column (one) with short strings. I
[quoted text clipped - 3 lines]
> Thanks
> Marek

Reply to this Message

MarekZ - 23 Jul 2007 17:09 GMT

Użytkownik bj napisał:

> if the strings are in column A, in B1 enter
> =countif(A:A,A1)
[quoted text clipped - 12 lines]
>>Thanks
>>Marek

Yes
Some strings looks like:

Account Data
Account information
Account not found
Account Type

Regards

Reply to this Message

bj - 23 Jul 2007 21:38 GMT

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

> Użytkownik bj napisał:
> > if the strings are in column A, in B1 enter
[quoted text clipped - 23 lines]
>
> Regards

Reply to this Message

Harlan Grove - 23 Jul 2007 23:59 GMT

"bj" <bj@discussions.microsoft.com> wrote...

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

...

>>Account Data
>>Account information
>>Account not found
>>Account Type

No need for SUMPRODUCT. Use

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

or if Account were entered in, say, C1,

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

Reply to this Message