Excel 2003 Counting multiple number entries in a single cell

[watermt]

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

 

[NBVC]

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

So if you have a list of numbers in A1:A5 like so:

1
2, 3, 4
5
6,7
8, 9, 0

then try this:

=SUM(LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5,",",""))+1)

confirmed with CTRL+SHIFT+ENTER not just ENTER..

this should give result of 10.. since there are 10 numbers in those 5
cells.


--
NBVC

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com)

 

[T. Valko]

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>

 

[watermt]

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]

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!!!

 

[watermt]

When I press CTRL+SHIFT, then hit ENTER the formula appears in the cell?
What might I be doing wrong?

 

[watermt]

Also, I'm not following you whenyou say C1 = 1, where does this come into
play in the formula or on the worksheet?

 

[watermt]

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&