Stacking numbers

[Highworld]

Hello, Not sure how to label this one. I am wondering if there is a way or another formula where you can stack a number such as showing 1, 2, 3, 4 (as Cell e10 is showing) but still have those who up in different columns such as the spread sheet attached. Trying to make it a smaller document without additional copying lines.

Book1.xlsx
	A	B	C	D	E	F	G	H	I
1
2
3		JANUARY
4		WEEK 1		WEEK 2		WEEK 3		WEEK 4
5		#	$	#	$	#	$	#	$
6		2	$329,652	0	$329,652	0	$329,652	0	$0
7
8
9		Number	Amount	Name	Week	Closed
10		?????	$1,000.00	John	1, 2, 3,4
11		?????	$10,000.00	Larry	1
12		????	$10,000.00	Jim	1
13		????	$1,111.00	Larry	2
14		???	$1,112.00	Jim	2
15		????	$12,365.00	Larry	3
16		????	$12,346.00	Jim	3
17		???	$12,354.00	Larry	4
18		???	$12,325,436.00	Jim	4
19
Sheet1

Cell Formulas
Range	Formula
B6	B6	=COUNTIF(E10:E18,1)
D6	D6	=COUNTIF(G10:G18,2)
F6	F6	=COUNTIF(G10:G18,3)
H6	H6	=COUNTIF(G10:G18,4)

 

[KRice]

What you want to do is not clear. You can combine 1,2,3,4 in a cell, but what logic would lead to that? And if that is done, then the COUNTIF formulas will not deliver the correct results. For example, see the COUNTIF(xxx,1), which returns 2, because it "sees" only two cells that equal 1. The John 1,2,3,4 cell is not counted.

 

[Highworld]

What you want to do is not clear. You can combine 1,2,3,4 in a cell, but what logic would lead to that? And if that is done, then the COUNTIF formulas will not deliver the correct results. For example, see the COUNTIF(xxx,1), which returns 2, because it "sees" only two cells that equal 1. The John 1,2,3,4 cell is not count

Would there be a formula that would work similarly to Countif in order to be able to do that, or would there be a way to combine multiple formulas that override each other, such as with Or?

 

[Cubist]

Is this what you're looking for?

Book1.xlsx
	A	B	C	D	E	F	G	H	I
1
2
3		JANUARY
4		WEEK 1		WEEK 2		WEEK 3		WEEK 4
5		#	$	#	$	#	$	#	$
6		3	329652	3	329652	3	329652	3	0
7
8
9		Number	Amount	Name	Week	Closed
10		?????	1000	John	1, 2, 3,4
11		?????	10000	Larry	1
12		????	10000	Jim	1
13		????	1111	Larry	2
14		???	1112	Jim	2
15		????	12365	Larry	3
16		????	12346	Jim	3
17		???	12354	Larry	4
18		???	12325436	Jim	4
Sheet3

Cell Formulas
Range	Formula
B6	B6	=SUM(--NOT(ISERROR(FIND(1,$E$10:$E$18))))
D6	D6	=SUM(--NOT(ISERROR(FIND(2,$E$10:$E$18))))
F6	F6	=SUM(--NOT(ISERROR(FIND(3,$E$10:$E$18))))
H6	H6	=SUM(--NOT(ISERROR(FIND(4,$E$10:$E$18))))

 

[Peter_SSs]

Is this what you're looking for?

I think those formulas are 'risky'. Consider them here

24 03 16.xlsm
	B	C	D	E	F	G	H	I
4	WEEK 1		WEEK 2		WEEK 3		WEEK 4
5	#	$	#	$	#	$	#	$
6	6	329652	3	329652	1	329652	3	0
7
8
9	Number	Amount	Name	Week	Closed
10	?????	1000	John	1, 2, 3,4
11	?????	10000	Larry	1
12	????	10000	Jim	1
13	????	1111	Larry	2
14	???	1112	Jim	11
15	????	12365	Larry	201
16	????	12346	Jim	510
17	???	12354	Larry	4
18	???	12325436	Jim	4
Count

Cell Formulas
Range	Formula
B6	B6	=SUM(--NOT(ISERROR(FIND(1,$E$10:$E$18))))
D6	D6	=SUM(--NOT(ISERROR(FIND(2,$E$10:$E$18))))
F6	F6	=SUM(--NOT(ISERROR(FIND(3,$E$10:$E$18))))
H6	H6	=SUM(--NOT(ISERROR(FIND(4,$E$10:$E$18))))

where you can stack a number such as showing 1, 2, 3, 4 (as Cell e10 is showing)

My suggestion is below but it relies on the "stacking" of numbers being uniform. The numbers in your cell E10 are not as the red numbers above (no space before the 4 in your cell E10).
Assuming that number-stacking is uniform then you could try this. (I have changed a few of the sample values in E10:E18, including adding a space before the 4 in E10)

24 03 16.xlsm
	B	C	D	E	F	G	H	I
3	JANUARY
4	WEEK 1		WEEK 2		WEEK 3		WEEK 4
5	#	$	#	$	#	$	#	$
6	3	329652	3	329652	3	329652	1	0
7
8
9	Number	Amount	Name	Week	Closed
10	?????	1000	John	1, 2, 3, 4
11	?????	10000	Larry	1
12	????	10000	Jim	1
13	????	1111	Larry	2
14	???	1112	Jim	2
15	????	12365	Larry	3
16	????	12346	Jim	3
17	???	12354	Larry	11
18	???	12325436	Jim	11
Count (2)

Cell Formulas
Range	Formula
B6,H6,F6,D6	B6	=COUNT(FILTER($C10:$C18,ISNUMBER(FIND(" "&MID(B4,6,9)&","," "&$E10:$E18&",")),""))