COUNTIF and LEFT/MID/RIGHT

[Woodpile]

Hello all,

I am hoping this can be done. I have tried to configure every combination I can think of but I am not skilled enough with Excel to figure it out.

Here is the situation:

I have a range of cells (C2:N156) with data in them. This data is a 3-digit number in each cell (stored as text because of leading zeroes) and I would like to create a simple chart totaling how many times of each single digit appears.

I have used the LEFT, MID and RIGHT functions to separate out this information before but I need to do it on a larger scale. I am hoping there is a way to combine COUNTIF and LEFT/MID/RIGHT in a range of cells.

I hope I am explaining this well enough. What I tried was something like this =COUNTIF(C2:N156, ((LEFT, 1) =1)), hoping it would return the number of times "1" appears in the first position in each cell in the range C2:N156. From there I can adjust the formula to meet the other 9 possible digits and the other two positions.

I tried variations on this theme and it appears LEFT in the example above cannot be qualified to a single value.

Overall, I am trying to create a graph showing the distribution of each digit to prove randomness. The data is from a raffle-type fundraiser I run for a non-profit organization I belong to. This raffle is licensed through the state and if I am audited, I would like to easily prove our method for drawing the numbers is truly random.

Thanks, at least, for reading this far. I am hoping the Excel gurus here can help!

Ed

 

[Snakehips]

Ed,

Not a ful set of data but maybe this will help....

Array formula in Q2 and drag down and across.

Excel 2007
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1														Digit	1st Pos	2nd Pos	3rd Pos
2	024	895	003	024	895	003	024	895	003	024	895	003		0	32	16	1
3	129	353	472	129	353	472	129	353	472	129	353	472		1	15	0	0
4	024	895	003	024	895	003	024	895	003	024	890	003		2	0	31	16
5	129	353	472	129	353	972	129	353	472	129	353	472		3	16	0	32
6	024	895	003	024	895	003	024	895	003	024	895	003		4	15	0	16
7	129	353	472	129	353	472	129	353	472	129	353	472		5	0	16	15
8	024	895	003	024	895	003	024	895	003	024	895	003		6	0	0	0
9	129	353	472	899	353	472	129	353	472	129	353	472		7	0	16	0
10														8	17	0	0
11														9	1	17	16
Sheet9

Cell Formulas
Range	Formula
Q2	{=COUNT(1/(MID($C$2:$N$156,COLUMNS($Q:Q),1) =TEXT($P2,"@")))}
Press CTRL+SHIFT+ENTER to enter array formulas.

[TABLE="width: 85%"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[FormR]

Here is a slightly different approach using the same set up as Tony:

Excel Workbook
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1														Digit	1st Pos	2nd Pos	3rd Pos
2	024	895	003	024	895	003	024	895	003	024	895	003		0	32	16	1
3	129	353	472	129	353	472	129	353	472	129	353	472		1	15	0	0
4	024	895	003	024	895	003	024	895	003	024	890	003		2	0	31	16
5	129	353	472	129	353	972	129	353	472	129	353	472		3	16	0	32
6	024	895	003	024	895	003	024	895	003	024	895	003		4	15	0	16
7	129	353	472	129	353	472	129	353	472	129	353	472		5	0	16	15
8	024	895	003	024	895	003	024	895	003	024	895	003		6	0	0	0
9	129	353	472	899	353	472	129	353	472	129	353	472		7	0	16	0
10														8	17	0	0
11														9	1	17	16
Sheet1

 

[Woodpile]

Hello Tony,

Thank you for the response. I think I followed your instructions and pasted the formula in to cell Q2 without the brackets. Excel did not insert those brackets and the formula returns a "0" in all cells but the formula does change where it is not locked by the $.

I am running Excel 2007 on a Windows platform so I used CRTL-V to paste into the cell. I also typed it in manually with the same result.

I do appreciate your effort and response.

Ed

 

[Woodpile]

FormR,

That one worked. Thanks for your help.

I appreciate the response.

Ed

 

[Snakehips]

Ed,

You are right to go with Form's tidier, non-array entry solution.

Just for the record, it sounds very much as if you did not enter my formula correctly. To enter an array formula.... With the cursor in the formula bar hold down the Ctrl and Shift keys and then hit Enter.