Counting multiple values within single cells
 

Is there a way I can do this?

I have a spreadsheet with a list of branches in cells A1 - A300. I then
have columns of data for each of the past 5 years (ie; B - F) and want to
count the number of instances where particular items occur in each column
(ie; 'apples' or 'oranges', etc). The difficulty is that in some cells
there is more than one item shown ... eg: cell C10 may have 'apples' and
'oranges' - the data is entered this way using 'Alt+Enter' to delimit the 2
items as I don't want rows with empty cells.

Is there a way of counting the items even though they are not in separate
cells? Or is there a better way of approaching this?

Many thanks.

Counting multiple values within single cells
 

Use =COUNTIF(B1:B500,"*apples*")
This worked for me with "apples pears" in one cell with LF (linefeed)
between the two words.
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Terry Bennett" wrote in message
...
Is there a way I can do this?

I have a spreadsheet with a list of branches in cells A1 - A300. I then
have columns of data for each of the past 5 years (ie; B - F) and want to
count the number of instances where particular items occur in each column
(ie; 'apples' or 'oranges', etc). The difficulty is that in some cells
there is more than one item shown ... eg: cell C10 may have 'apples' and
'oranges' - the data is entered this way using 'Alt+Enter' to delimit the
2 items as I don't want rows with empty cells.

Is there a way of counting the items even though they are not in separate
cells? Or is there a better way of approaching this?

Many thanks.

Counting multiple values within single cells
 

Thanks Bernard - that partially works. It seems to count the number of
instances of cells that contain the word in question. What I really need is
something that will actually count the number of those words in the whole
column (sometimes the same word can occur twice within a cell).

"Bernard Liengme" wrote in message
...
Use =COUNTIF(B1:B500,"*apples*")
This worked for me with "apples pears" in one cell with LF (linefeed)
between the two words.
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Terry Bennett" wrote in message
...
Is there a way I can do this?

I have a spreadsheet with a list of branches in cells A1 - A300. I then
have columns of data for each of the past 5 years (ie; B - F) and want to
count the number of instances where particular items occur in each column
(ie; 'apples' or 'oranges', etc). The difficulty is that in some cells
there is more than one item shown ... eg: cell C10 may have 'apples' and
'oranges' - the data is entered this way using 'Alt+Enter' to delimit the
2 items as I don't want rows with empty cells.

Is there a way of counting the items even though they are not in separate
cells? Or is there a better way of approaching this?

Many thanks.

Counting multiple values within single cells
 

This appeared to work for me (counting the number of occurrences of apple in
a1:a8 where sometimes apple occurred more than once in a cell:

=SUMPRODUCT((LEN(A1:A8)-LEN(SUBSTITUTE(A1:A8,"apples","")))/LEN("apples"))

--
Kevin Vaughn


"Terry Bennett" wrote:

Thanks Bernard - that partially works. It seems to count the number of
instances of cells that contain the word in question. What I really need is
something that will actually count the number of those words in the whole
column (sometimes the same word can occur twice within a cell).

"Bernard Liengme" wrote in message
...
Use =COUNTIF(B1:B500,"*apples*")
This worked for me with "apples pears" in one cell with LF (linefeed)
between the two words.
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Terry Bennett" wrote in message
...
Is there a way I can do this?

I have a spreadsheet with a list of branches in cells A1 - A300. I then
have columns of data for each of the past 5 years (ie; B - F) and want to
count the number of instances where particular items occur in each column
(ie; 'apples' or 'oranges', etc). The difficulty is that in some cells
there is more than one item shown ... eg: cell C10 may have 'apples' and
'oranges' - the data is entered this way using 'Alt+Enter' to delimit the
2 items as I don't want rows with empty cells.

Is there a way of counting the items even though they are not in separate
cells? Or is there a better way of approaching this?

Many thanks.

Counting multiple values within single cells
 

Thanks Kevin - that seems to work well!

"Kevin Vaughn" wrote in message
...
This appeared to work for me (counting the number of occurrences of apple
in
a1:a8 where sometimes apple occurred more than once in a cell:

=SUMPRODUCT((LEN(A1:A8)-LEN(SUBSTITUTE(A1:A8,"apples","")))/LEN("apples"))

--
Kevin Vaughn


"Terry Bennett" wrote:

Thanks Bernard - that partially works. It seems to count the number of
instances of cells that contain the word in question. What I really need
is
something that will actually count the number of those words in the whole
column (sometimes the same word can occur twice within a cell).

"Bernard Liengme" wrote in message
...
Use =COUNTIF(B1:B500,"*apples*")
This worked for me with "apples pears" in one cell with LF (linefeed)
between the two words.
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Terry Bennett" wrote in message
...
Is there a way I can do this?

I have a spreadsheet with a list of branches in cells A1 - A300. I
then
have columns of data for each of the past 5 years (ie; B - F) and want
to
count the number of instances where particular items occur in each
column
(ie; 'apples' or 'oranges', etc). The difficulty is that in some
cells
there is more than one item shown ... eg: cell C10 may have 'apples'
and
'oranges' - the data is entered this way using 'Alt+Enter' to delimit
the
2 items as I don't want rows with empty cells.

Is there a way of counting the items even though they are not in
separate
cells? Or is there a better way of approaching this?

Many thanks.

Counting multiple values within single cells
 

You're welcome.
--
Kevin Vaughn


"Terry Bennett" wrote:

Thanks Kevin - that seems to work well!

"Kevin Vaughn" wrote in message
...
This appeared to work for me (counting the number of occurrences of apple
in
a1:a8 where sometimes apple occurred more than once in a cell:

=SUMPRODUCT((LEN(A1:A8)-LEN(SUBSTITUTE(A1:A8,"apples","")))/LEN("apples"))

--
Kevin Vaughn


"Terry Bennett" wrote:

Thanks Bernard - that partially works. It seems to count the number of
instances of cells that contain the word in question. What I really need
is
something that will actually count the number of those words in the whole
column (sometimes the same word can occur twice within a cell).

"Bernard Liengme" wrote in message
...
Use =COUNTIF(B1:B500,"*apples*")
This worked for me with "apples pears" in one cell with LF (linefeed)
between the two words.
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Terry Bennett" wrote in message
...
Is there a way I can do this?

I have a spreadsheet with a list of branches in cells A1 - A300. I
then
have columns of data for each of the past 5 years (ie; B - F) and want
to
count the number of instances where particular items occur in each
column
(ie; 'apples' or 'oranges', etc). The difficulty is that in some
cells
there is more than one item shown ... eg: cell C10 may have 'apples'
and
'oranges' - the data is entered this way using 'Alt+Enter' to delimit
the
2 items as I don't want rows with empty cells.

Is there a way of counting the items even though they are not in
separate
cells? Or is there a better way of approaching this?

Many thanks.