Counting multiple values within single cells

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Terry Bennett - 24 Oct 2006 15:02 GMT

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.

Reply to this Message

Bernard Liengme - 24 Oct 2006 15:24 GMT

Use =COUNTIF(B1:B500,"*apples*")
This worked for me with "apples pears" in one cell with LF (linefeed)
between the two words.
best wishes

Signature

Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

> Is there a way I can do this?
>
[quoted text clipped - 10 lines]
>
> Many thanks.

Reply to this Message

Terry Bennett - 24 Oct 2006 15:48 GMT

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).

> Use =COUNTIF(B1:B500,"*apples*")
> This worked for me with "apples pears" in one cell with LF (linefeed)
[quoted text clipped - 14 lines]
>>
>> Many thanks.

Reply to this Message

Kevin Vaughn - 24 Oct 2006 16:22 GMT

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"))

Signature

Kevin Vaughn

> 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
[quoted text clipped - 19 lines]
> >>
> >> Many thanks.

Reply to this Message

Terry Bennett - 24 Oct 2006 17:30 GMT

Thanks Kevin - that seems to work well!

> This appeared to work for me (counting the number of occurrences of apple
> in
[quoted text clipped - 33 lines]
>> >>
>> >> Many thanks.

Reply to this Message

Kevin Vaughn - 24 Oct 2006 18:10 GMT

You're welcome.

Signature

Kevin Vaughn

> Thanks Kevin - that seems to work well!
>
[quoted text clipped - 35 lines]
> >> >>
> >> >> Many thanks.

Reply to this Message