Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
offsetting values of multiple cells! | Excel Worksheet Functions | |||
percenatge increase in values across multiple cells | New Users to Excel | |||
How to sum the values of a single cell from multiple worksheets | Excel Worksheet Functions | |||
Pasting single cells from Word to multiple cells in Excel | Excel Worksheet Functions | |||
Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions |