| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2003 Counting multiple number entries in a single cell
I need to come up with a formula (e.g., COUNTIF, VLOOKUP) that will look at
each cell in a column and count the number of occurence for each number in all cells in the column. I'm not sure which formula to use or which 'seperator' (i.e., comma, dash, etc.) to use within the cell. My numbers are inclusive of 1 through 43, and I do not want to count the number 1 as number 1 when it appears as 10, 11, 12, 21, 31, 41 and so on. I thank you in advance for any and all assistance, Mike |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 2003 Counting multiple number entries in a single cell
Let's assume you have this data:
A1 = 1,5,10,11,21 A2 = 12,5,31,32,1 A3 = 1,7,10,21,39 A4 = 1,2,3,1,11 Number delimited by commas. To count how many instances of the number 1... C1 = 1 Array entered** : =SUM(LEN(","&A$1:A$4&",")-LEN(SUBSTITUTE(","&A$1:A$4&",",","&C1&",","")))/LEN(","&C1&",") Result = 5 ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. That's very cryptic and may induce headaches if you stare at it too long! <g -- Biff Microsoft Excel MVP "watermt" wrote in message ... I need to come up with a formula (e.g., COUNTIF, VLOOKUP) that will look at each cell in a column and count the number of occurence for each number in all cells in the column. I'm not sure which formula to use or which 'seperator' (i.e., comma, dash, etc.) to use within the cell. My numbers are inclusive of 1 through 43, and I do not want to count the number 1 as number 1 when it appears as 10, 11, 12, 21, 31, 41 and so on. I thank you in advance for any and all assistance, Mike |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 2003 Counting multiple number entries in a single cell
This looks like it will work but I inadvertently omitted one piece of
information which is crucial. The numbers which are being counted are on a wroksheet named Aug 09, but the result of the SUM foemula worksheet is titled DietIssuesCodes. Where in the formula would I insert each worksheets name? Mike "T. Valko" wrote: Let's assume you have this data: A1 = 1,5,10,11,21 A2 = 12,5,31,32,1 A3 = 1,7,10,21,39 A4 = 1,2,3,1,11 Number delimited by commas. To count how many instances of the number 1... C1 = 1 Array entered** : =SUM(LEN(","&A$1:A$4&",")-LEN(SUBSTITUTE(","&A$1:A$4&",",","&C1&",","")))/LEN(","&C1&",") Result = 5 ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. That's very cryptic and may induce headaches if you stare at it too long! <g -- Biff Microsoft Excel MVP "watermt" wrote in message ... I need to come up with a formula (e.g., COUNTIF, VLOOKUP) that will look at each cell in a column and count the number of occurence for each number in all cells in the column. I'm not sure which formula to use or which 'seperator' (i.e., comma, dash, etc.) to use within the cell. My numbers are inclusive of 1 through 43, and I do not want to count the number 1 as number 1 when it appears as 10, 11, 12, 21, 31, 41 and so on. I thank you in advance for any and all assistance, Mike |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 2003 Counting multiple number entries in a single cell
Try this:
=SUM(LEN(","&'Aug 09'!A$1:A$4&",")-LEN(SUBSTITUTE(","&'Aug 09'!A$1:A$4&",",","&C1&",","")))/LEN(","&C1&",") Don't forget, array entered!!! -- Biff Microsoft Excel MVP "watermt" wrote in message ... This looks like it will work but I inadvertently omitted one piece of information which is crucial. The numbers which are being counted are on a wroksheet named Aug 09, but the result of the SUM foemula worksheet is titled DietIssuesCodes. Where in the formula would I insert each worksheets name? Mike "T. Valko" wrote: Let's assume you have this data: A1 = 1,5,10,11,21 A2 = 12,5,31,32,1 A3 = 1,7,10,21,39 A4 = 1,2,3,1,11 Number delimited by commas. To count how many instances of the number 1... C1 = 1 Array entered** : =SUM(LEN(","&A$1:A$4&",")-LEN(SUBSTITUTE(","&A$1:A$4&",",","&C1&",","")))/LEN(","&C1&",") Result = 5 ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. That's very cryptic and may induce headaches if you stare at it too long! <g -- Biff Microsoft Excel MVP "watermt" wrote in message ... I need to come up with a formula (e.g., COUNTIF, VLOOKUP) that will look at each cell in a column and count the number of occurence for each number in all cells in the column. I'm not sure which formula to use or which 'seperator' (i.e., comma, dash, etc.) to use within the cell. My numbers are inclusive of 1 through 43, and I do not want to count the number 1 as number 1 when it appears as 10, 11, 12, 21, 31, 41 and so on. I thank you in advance for any and all assistance, Mike |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 2003 Counting multiple number entries in a single cell
When I press CTRL+SHIFT, then hit ENTER the formula appears in the cell?
What might I be doing wrong? "T. Valko" wrote: Try this: =SUM(LEN(","&'Aug 09'!A$1:A$4&",")-LEN(SUBSTITUTE(","&'Aug 09'!A$1:A$4&",",","&C1&",","")))/LEN(","&C1&",") Don't forget, array entered!!! -- Biff Microsoft Excel MVP "watermt" wrote in message ... This looks like it will work but I inadvertently omitted one piece of information which is crucial. The numbers which are being counted are on a wroksheet named Aug 09, but the result of the SUM foemula worksheet is titled DietIssuesCodes. Where in the formula would I insert each worksheets name? Mike "T. Valko" wrote: Let's assume you have this data: A1 = 1,5,10,11,21 A2 = 12,5,31,32,1 A3 = 1,7,10,21,39 A4 = 1,2,3,1,11 Number delimited by commas. To count how many instances of the number 1... C1 = 1 Array entered** : =SUM(LEN(","&A$1:A$4&",")-LEN(SUBSTITUTE(","&A$1:A$4&",",","&C1&",","")))/LEN(","&C1&",") Result = 5 ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. That's very cryptic and may induce headaches if you stare at it too long! <g -- Biff Microsoft Excel MVP "watermt" wrote in message ... I need to come up with a formula (e.g., COUNTIF, VLOOKUP) that will look at each cell in a column and count the number of occurence for each number in all cells in the column. I'm not sure which formula to use or which 'seperator' (i.e., comma, dash, etc.) to use within the cell. My numbers are inclusive of 1 through 43, and I do not want to count the number 1 as number 1 when it appears as 10, 11, 12, 21, 31, 41 and so on. I thank you in advance for any and all assistance, Mike |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 2003 Counting multiple number entries in a single cell
Also, I'm not following you whenyou say C1 = 1, where does this come into
play in the formula or on the worksheet? "watermt" wrote: When I press CTRL+SHIFT, then hit ENTER the formula appears in the cell? What might I be doing wrong? "T. Valko" wrote: Try this: =SUM(LEN(","&'Aug 09'!A$1:A$4&",")-LEN(SUBSTITUTE(","&'Aug 09'!A$1:A$4&",",","&C1&",","")))/LEN(","&C1&",") Don't forget, array entered!!! -- Biff Microsoft Excel MVP "watermt" wrote in message ... This looks like it will work but I inadvertently omitted one piece of information which is crucial. The numbers which are being counted are on a wroksheet named Aug 09, but the result of the SUM foemula worksheet is titled DietIssuesCodes. Where in the formula would I insert each worksheets name? Mike "T. Valko" wrote: Let's assume you have this data: A1 = 1,5,10,11,21 A2 = 12,5,31,32,1 A3 = 1,7,10,21,39 A4 = 1,2,3,1,11 Number delimited by commas. To count how many instances of the number 1... C1 = 1 Array entered** : =SUM(LEN(","&A$1:A$4&",")-LEN(SUBSTITUTE(","&A$1:A$4&",",","&C1&",","")))/LEN(","&C1&",") Result = 5 ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. That's very cryptic and may induce headaches if you stare at it too long! <g -- Biff Microsoft Excel MVP "watermt" wrote in message ... I need to come up with a formula (e.g., COUNTIF, VLOOKUP) that will look at each cell in a column and count the number of occurence for each number in all cells in the column. I'm not sure which formula to use or which 'seperator' (i.e., comma, dash, etc.) to use within the cell. My numbers are inclusive of 1 through 43, and I do not want to count the number 1 as number 1 when it appears as 10, 11, 12, 21, 31, 41 and so on. I thank you in advance for any and all assistance, Mike |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 2003 Counting multiple number entries in a single cell
Okay, I got the array formula entered, sticky keys on the keyboard - force
helps sometimes! But now, I only get a count of the number 1 from the first cell in my worksheet which is S3. It's not looking for the number 1 in all cells: &'Aug 09'!S$3:S$178& "watermt" wrote: When I press CTRL+SHIFT, then hit ENTER the formula appears in the cell? What might I be doing wrong? "T. Valko" wrote: Try this: =SUM(LEN(","&'Aug 09'!A$1:A$4&",")-LEN(SUBSTITUTE(","&'Aug 09'!A$1:A$4&",",","&C1&",","")))/LEN(","&C1&",") Don't forget, array entered!!! -- Biff Microsoft Excel MVP "watermt" wrote in message ... This looks like it will work but I inadvertently omitted one piece of information which is crucial. The numbers which are being counted are on a wroksheet named Aug 09, but the result of the SUM foemula worksheet is titled DietIssuesCodes. Where in the formula would I insert each worksheets name? Mike "T. Valko" wrote: Let's assume you have this data: A1 = 1,5,10,11,21 A2 = 12,5,31,32,1 A3 = 1,7,10,21,39 A4 = 1,2,3,1,11 Number delimited by commas. To count how many instances of the number 1... C1 = 1 Array entered** : =SUM(LEN(","&A$1:A$4&",")-LEN(SUBSTITUTE(","&A$1:A$4&",",","&C1&",","")))/LEN(","&C1&",") Result = 5 ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. That's very cryptic and may induce headaches if you stare at it too long! <g -- Biff Microsoft Excel MVP "watermt" wrote in message ... I need to come up with a formula (e.g., COUNTIF, VLOOKUP) that will look at each cell in a column and count the number of occurence for each number in all cells in the column. I'm not sure which formula to use or which 'seperator' (i.e., comma, dash, etc.) to use within the cell. My numbers are inclusive of 1 through 43, and I do not want to count the number 1 as number 1 when it appears as 10, 11, 12, 21, 31, 41 and so on. I thank you in advance for any and all assistance, Mike |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counting number of times a specific word appears in a single cell | Excel Discussion (Misc queries) | |||
| How to sort ascending or descending in multiple entries within one single cell? | Excel Discussion (Misc queries) | |||
| Excel 2003: Autofilter with Multiple Entries | Excel Discussion (Misc queries) | |||
| Counting a single value across multiple cell groups | Excel Discussion (Misc queries) | |||
| Counting number of row entries | Excel Discussion (Misc queries) | |||
Hybrid Mode
