counting frequency of strings in cells
 

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

counting frequency of strings in cells
 

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

tom ossieur wrote:
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

counting frequency of strings in cells
 

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,

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

counting frequency of strings in cells
 

A none case sensitive version

=(LEN(UPPER(A1))-LEN(SUBSTITUTE(UPPER(A1),"PORT SAID","")))/LEN("PORT SAID")

Sorry I meant to say for column D edit the text to Damietta and put it in
upper case to make it none case sensitive.

"Mike" wrote:

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.

"tom ossieur" wrote:

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

counting frequency of strings in cells
 

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.

"tom ossieur" wrote:

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