Find total of columns of each row in a single cell -Excel 2007

[MyTaxcel]

Can anybody help me to find total of all the horizontal elements in each row of an array in a single cell without VBA code..

Values shown in the following table are basically the elements of an array derived from other excel formulas.

Purpose is to find the row number at which sum of horizontal elements is greater than a certain value.

Book1
	A	B	C	D	E
1	0	0	0		={0;1;2;5;3;9;6;4;5;24}
2	0	0	1
3	1	1	0
4	0	0	0	5
5	1	1	1	0
6	3	3	3
7	2	2	2
8	0	0	0	4
9	1	1	1	2
10	8	8	8
Sheet1

 

[BSALV]

oei, in 2007, what functions exists at that moment ???

Map1
	A	B	C	D	E	F	G
1	0	0	0			0	0, 1, 2, 5, 3, 9, 6, 4, 5, 24,
2	0	0	1			1	1, 2, 5, 3, 9, 6, 4, 5, 24,
3	1	1	0			2	2, 5, 3, 9, 6, 4, 5, 24,
4	0	0	0	5		5	5, 3, 9, 6, 4, 5, 24,
5	1	1	1	0		3	3, 9, 6, 4, 5, 24,
6	3	3	3			9	9, 6, 4, 5, 24,
7	2	2	2			6	6, 4, 5, 24,
8	0	0	0	4		4	4, 5, 24,
9	1	1	1	2		5	5, 24,
10	8	8	8			24	24,
11
Blad1

Cell Formulas
Range	Formula
F1:F10	F1	=SUM(A1:D1)
G1:G11	G1	=IF(F1<>"",F1 & ", ","") &G2

 

[MyTaxcel]

Thank you very much for your quick reply ..

Can it be done via a single cell formula ? As I mentioned that data in range A1:D10 (as shown) does not come from the excel sheet, It is a result of excel formula (as an array).. therefore I can not refer to cells or help columns to arrive at single cell result.

oei, in 2007, what functions exists at that moment ???

Map1
	A	B	C	D	E	F	G
1	0	0	0			0	0, 1, 2, 5, 3, 9, 6, 4, 5, 24,
2	0	0	1			1	1, 2, 5, 3, 9, 6, 4, 5, 24,
3	1	1	0			2	2, 5, 3, 9, 6, 4, 5, 24,
4	0	0	0	5		5	5, 3, 9, 6, 4, 5, 24,
5	1	1	1	0		3	3, 9, 6, 4, 5, 24,
6	3	3	3			9	9, 6, 4, 5, 24,
7	2	2	2			6	6, 4, 5, 24,
8	0	0	0	4		4	4, 5, 24,
9	1	1	1	2		5	5, 24,
10	8	8	8			24	24,
11
Blad1

Cell Formulas
Range	Formula
F1:F10	F1	=SUM(A1:D1)
G1:G11	G1	=IF(F1<>"",F1 & ", ","") &G2

 

[BSALV]

and how that your formula/array look like for the moment ?
I have no clue.

 

[MyTaxcel]

and how that your formula/array look like for the moment ?
I have no clue.

I am comparing a List of Names against a column containing a large number of company names..

For example see the attached table

Book1
	A	B	C	D	E	F
1	Party Names		_Name List
2	Teotia Plastic Industries		Teotia		Formula	=(LEN($A$2:$A$11)-LEN(SUBSTITUTE($A$2:$A$11,TRANSPOSE($C$2:$C$5),"")))/LEN(TRANSPOSE($C$2:$C$5))
3	Telu Raman febrics		Plastic		Result	={1,1,1,0 ; 0,0,0,0 ; 1,1,0,0 ; 0,0,0,0 ; 0,0,0,1; 0,0,1,1 ; 0,1,1,1 ; 0,0,0,0 ; 1,0,0,1; 0,0,0,0}
4	Teotia Plastic Company Limited		Industries
5	Kedar Nath Garments		Mohan			(This means 1st Row has three matches from the name list, Second row None, Third Row 2 Matches so on..)
6	Mohan Raj Company
7	Mohan Raj Industries
8	Mohan Plastic Industries
9	Aero Club Plot No
10	Teotia Mohan James
11	Aish Industry
12						I have to find row first row where number of matches are greater than say (2 or 3) for which I have to add elements of each row ofarray produced by the formula and I have will have to do it for every row
Sheet3

 

[BSALV]

Map1
	A	B	C	D	E	F	G	H
1	Party Names		_Name List			2	3	4
2	Teotia Plastic Industries		Teotia	3		-	Teotia	Mohan
3	Telu Raman febrics		Plastic	3
4	Teotia Plastic Company Limited		Industries	3
5	Kedar Nath Garments		Mohan	4
6	Mohan Raj Company
7	Mohan Raj Industries
8	Mohan Plastic Industries
9	Aero Club Plot No
10	Teotia Mohan James
11	Aish Industry
Blad1

Cell Formulas
Range	Formula
D2:D5	D2	=COUNTIF($A$2:$A$11,"*"&_name_list&"*")
F2:H2	F2	=IFERROR(INDEX(_name_list,MATCH(F$1,COUNTIF($A$2:$A$11,"*"&_name_list&"*"),0)),"-")
G8	G8	=CONCATENATE(D7:G7)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
_name_list	=Blad1!$C$2:$C$5	F2:H2, D2

 

[BSALV]

Map1
	A	B	C	D	E	F	G	H
1	Party Names		_Name List			2	3	4
2	Teotia Plastic Industries		Teotia	3		-	Teotia	Mohan
3	Telu Raman febrics		Plastic	3
4	Teotia Plastic Company Limited		Industries	3		-	Teotia	Mohan
5	Kedar Nath Garments		Mohan	4		-	Plastic	-
6	Mohan Raj Company					-	Industries	-
7	Mohan Raj Industries					-	-	-
8	Mohan Plastic Industries					-	-	-
9	Aero Club Plot No					-	-	-
10	Teotia Mohan James					-	-	-
11	Aish Industry					-	-	-
12						-	-	-
Blad1

Cell Formulas
Range	Formula
D2:D5	D2	=COUNTIF($A$2:$A$11,"*"&_name_list&"*")
F2:H2	F2	=IFERROR(INDEX(_name_list,MATCH(F$1,COUNTIF($A$2:$A$11,"*"&_name_list&"*"),0)),"-")
F4:H12	F4	=IFERROR(INDEX(_name_list,MATCH(SMALL(IF(OFFSET(_name_list,,1,,)=F$1,OFFSET(_name_list,,1,,)+ROW(_name_list),"~"),ROW()-3),IF(OFFSET(_name_list,,1,,)=F$1,OFFSET(_name_list,,1,,)+ROW(_name_list),"~"),0)),"-")
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
_name_list	=Blad1!$C$2:$C$5	F4:H12, F2:H2, D2

 

[MyTaxcel]

Thank you very much.. I really appreciate your helping nature and Excel Skills

This works fine for me.

I have one more question.. can this calculation be done t be done on the Array produced in Cell F2 in the following table.. I think if we you use array produced in the cell F2 for further processing .. Offset formula won't work on it.. but Index formula can be used to know the row where ( 1+1+1+0 (sum of first row ); 0+0+0+0 (sum of second row) ; 1+1+0+0 (sum of third row and so.. on ) is greater than a certain number..

I mean ..I want to use array shown in F2 for further processing as there are a number of list which will be compared against the party name..

Book1
	A	B	C	D	E	F
1	Party Names		_Name List
2	Teotia Plastic Industries		Teotia		Formula	=(LEN($A$2:$A$11)-LEN(SUBSTITUTE($A$2:$A$11,TRANSPOSE($C$2:$C$5),"")))/LEN(TRANSPOSE($C$2:$C$5))
3	Telu Raman febrics		Plastic		Result	={1,1,1,0 ; 0,0,0,0 ; 1,1,0,0 ; 0,0,0,0 ; 0,0,0,1; 0,0,1,1 ; 0,1,1,1 ; 0,0,0,0 ; 1,0,0,1; 0,0,0,0}
4	Teotia Plastic Company Limited		Industries
5	Kedar Nath Garments		Mohan			(This means 1st Row has three matches from the name list, Second row None, Third Row 2 Matches so on..)
6	Mohan Raj Company
7	Mohan Raj Industries
8	Mohan Plastic Industries
9	Aero Club Plot No
10	Teotia Mohan James
11	Aish Industry
12						I have to find row first row where number of matches are greater than say (2 or 3) for which I have to add elements of each row ofarray produced by the formula and I have will have to do it for every row
Sheet3

Map1
	A	B	C	D	E	F	G	H
1	Party Names		_Name List			2	3	4
2	Teotia Plastic Industries		Teotia	3		-	Teotia	Mohan
3	Telu Raman febrics		Plastic	3
4	Teotia Plastic Company Limited		Industries	3		-	Teotia	Mohan
5	Kedar Nath Garments		Mohan	4		-	Plastic	-
6	Mohan Raj Company					-	Industries	-
7	Mohan Raj Industries					-	-	-
8	Mohan Plastic Industries					-	-	-
9	Aero Club Plot No					-	-	-
10	Teotia Mohan James					-	-	-
11	Aish Industry					-	-	-
12						-	-	-
Blad1

Cell Formulas
Range	Formula
D2:D5	D2	=COUNTIF($A$2:$A$11,"*"&_name_list&"*")
F2:H2	F2	=IFERROR(INDEX(_name_list,MATCH(F$1,COUNTIF($A$2:$A$11,"*"&_name_list&"*"),0)),"-")
F4:H12	F4	=IFERROR(INDEX(_name_list,MATCH(SMALL(IF(OFFSET(_name_list,,1,,)=F$1,OFFSET(_name_list,,1,,)+ROW(_name_list),"~"),ROW()-3),IF(OFFSET(_name_list,,1,,)=F$1,OFFSET(_name_list,,1,,)+ROW(_name_list),"~"),0)),"-")
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
_name_list	=Blad1!$C$2:$C$5	F4:H12, F2:H2, D2

 

[Fluff]

You can use this to sum the values in each row of the array.

=MMULT((LEN($A$2:$A$11)-LEN(SUBSTITUTE($A$2:$A$11,TRANSPOSE($C$2:$C$5),"")))/LEN(TRANSPOSE($C$2:$C$5)),ROW(C2:C5)^0)

 

[MyTaxcel]

You can use this to sum the values in each row of the array.

=MMULT((LEN($A$2:$A$11)-LEN(SUBSTITUTE($A$2:$A$11,TRANSPOSE($C$2:$C$5),"")))/LEN(TRANSPOSE($C$2:$C$5)),ROW(C2:C5)^0)

thank your very much..



Yes.. but I want solution for Excel 2007