counting frequency of strings in cells

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

tom ossieur - 28 Feb 2007 10:39 GMT

Hi,

Given the table below. In column C, D etc I want to get the result of how
frequent a certain port is mentioned.

    A    B
1
2    Route 1    Alexandria, Port Said, Damietta, Port Said, Alexandria
3    Route 2    Port Said, Damietta, Gebze, Izmir, Port Said,
4    Route 3    Port Said, Alexandria, Port Said, Damietta, Port Said
5    Route 4    Port Said, Beirut, Tartous, Limassol, Port Said
6    Route 5    Port Said, Damietta, Istanbul, Port Said

so if column C refers to Port Said and column D refers to Damietta, the
results should be:

    C    D
1    Port Said    Damietta
2    2    1
3    2    1
4    3    1
5    2    0
6    2    1

Is this possible?

Many thanks in advance and have a nice day!

tom

Reply to this Message

vezerid - 28 Feb 2007 11:26 GMT

Assuming you have port names in column 1 (i.e. Port Said in C1), then
you can use the following formula in C2 and copy down and across:

(LEN($B2)-LEN(SUBSTITUTE($B2,C$1,"")))/LEN(C$1)

HTH
Kostis Vezerides

> Hi,
>
[quoted text clipped - 25 lines]
>
> tom

Reply to this Message

Stefi - 28 Feb 2007 14:05 GMT

In cell C2 enter formula
=(LEN($B2)-LEN(SUBSTITUTE($B2,C$1,"")))/LEN(C$1)
and drag it to the right and down as necessary.

Regards,
Stefi

„tom ossieur” ezt írta:

> Hi,
>
[quoted text clipped - 25 lines]
>
> tom

Reply to this Message

Mike - 28 Feb 2007 15:00 GMT

In column C for Port Said use

=(LEN(A2)-LEN(SUBSTITUTE(A2,"Port Said","")))/LEN("Port Said")

And edit the text for other ports. Note this is case sensitive.

> Hi,
>
[quoted text clipped - 25 lines]
>
> tom

Reply to this Message