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

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

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

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

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

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

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

Excel 2003 Counting multiple number entries in a single cell
 

Here's a small sample file that demonstrates this:

xWater.xls 14kb

http://cjoint.com/?iCsXTgzUHJ

--
Biff
Microsoft Excel MVP


"watermt" wrote in message
...
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

Excel 2003 Counting multiple number entries in a single cell
 

Thanks T. Valko, your sample file cleared up all my questions and mistakes,
my count is completed and accurate.

Have a great weekend!

"T. Valko" wrote:

Here's a small sample file that demonstrates this:

xWater.xls 14kb

http://cjoint.com/?iCsXTgzUHJ

--
Biff
Microsoft Excel MVP


"watermt" wrote in message
...
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

Excel 2003 Counting multiple number entries in a single cell
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"watermt" wrote in message
...
Thanks T. Valko, your sample file cleared up all my questions and
mistakes,
my count is completed and accurate.

Have a great weekend!

"T. Valko" wrote:

Here's a small sample file that demonstrates this:

xWater.xls 14kb

http://cjoint.com/?iCsXTgzUHJ

--
Biff
Microsoft Excel MVP


"watermt" wrote in message
...
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