Help fixing Sumproduct formula

[michaelsmith559]

Need help fixing the sumproduct formula. I'm trying to get it to use the values in column N but I've tried a couple of different ways and I get a #value error or I manually have to change the reference in the formula to the value shown in column N when it becomes a double digit such as 10. Columns u,v,&w are where I was trying different formulas. Below is a partial copy of the sheet. Thanks for any help.

mm combinations.xlsm
	N	O	P	Q	R	S	T	U	V	W
1	Value	Combinations	Start Col	End Col	Start Row	End Row	1048576		VALUE(MID(A1,1,1))
2	1	864501	A	A	1	864501		1	864501
3	2	814385	A	B	864502	630310		2	814385
4	3	766480	B	C	630311	348214		3	487635	#VALUE!
5	4	720720	C	D	348215	20358		4
6	5	677040	D	D	20359	697398		5
7	6	635376	D	E	697399	284198
8	7	595665	E	E	284199	879863
9	8	557845	E	F	879864	389132
10	9	521855	F	F	389133	910987
11	10	487635	F	G	910988	350046
12	11	455126	G	G	350047	805172
13	12	424270	G	H	805173	180866
14	13	395010	H	H	180867	575876
15	14	367290	H	H	575877	943166
16	15	341055	H	I	943167	235645
Sheet2

Cell Formulas
Range	Formula
R2	R2	=T1-1048575
S2,S14:S15,S12,S10,S8,S6	S2	=(O2+R2)-1
R3:R16	R3	=S2+1
S16,S13,S11,S9,S7,S3:S5	S3	=(O3-($T$1-R3))-1
V2:V3	V2	=SUMPRODUCT(--(VALUE(MID(A:A,1,1))=N2)+(VALUE(MID(B:B,1,1))=N2))
V4	V4	=SUMPRODUCT(--((MID(F:F,1,2))="10")+((MID(G:G,1,2))="10"))
W4	W4	=SUMPRODUCT(--(VALUE(MID(F:F,1,2))=N11)+(VALUE(MID(G:G,1,2))=N11))
U2	U2	=VALUE(MID(A1,1,1))
U3	U3	=VALUE(MID(A1,4,1))
U4	U4	=VALUE(MID(A1,7,1))
U5	U5	=VALUE(MID(A1,10,1))
U6	U6	=VALUE(MID(A1,13,1))
O2:O3	O2	=SUMPRODUCT(--(LEFT(A:A,1)*1=N2)+(LEFT(B:B,1)*1=N2))
O4	O4	=SUMPRODUCT(--(LEFT(B:B,1)*1=N4)+(LEFT(C:C,1)*1=N4))
O5	O5	=SUMPRODUCT(--(LEFT(C:C,1)*1=N5)+(LEFT(D:D,1)*1=N5))
O6:O7	O6	=SUMPRODUCT(--(LEFT(D:D,1)*1=N6)+(LEFT(E:E,1)*1=N6))
O8:O10	O8	=SUMPRODUCT(--(LEFT(E:E,1)*1=N8)+(LEFT(F:F,1)*1=N8))
O11	O11	=SUMPRODUCT(--(LEFT(F:F,2)="10")+(LEFT(G:G,2)="10"))
O12	O12	=SUMPRODUCT(--(LEFT(G:G,2)="11")+(LEFT(H:H,2)="11"))
O13	O13	=SUMPRODUCT(--(LEFT(G:G,2)="12")+(LEFT(H:H,2)="12"))
O14	O14	=SUMPRODUCT(--(LEFT(H:H,2)="13")+(LEFT(I:I,2)="13"))
O15	O15	=SUMPRODUCT(--(LEFT(H:H,2)="14")+(LEFT(I:I,2)="14"))
O16	O16	=SUMPRODUCT(--(LEFT(H:H,2)="15")+(LEFT(I:I,2)="15"))

 

[Fluff]

As you haven't shown the data the formula is using & haven't said what you are trying to do, the following is a total guess

=SUMPRODUCT(--(VALUE(left(A:A,len(N2)))=N2)+(VALUE(left(B:B,len(N2)))=N2))

 

[awoohaw]

need to share the entire range for use to debug it for you

 

[michaelsmith559]

As you haven't shown the data the formula is using & haven't said what you are trying to do, the following is a total guess

=SUMPRODUCT(--(VALUE(left(A:A,len(N2)))=N2)+(VALUE(left(B:B,len(N2)))=N2))

Sorry, I am counting the number of combinations that start with the value listed in column N. Here is a sample of what the values look like:

mm combinations.xlsm
	A	B
1	1, 2, 3, 4, 5	2, 7, 10, 41, 52
2	1, 2, 3, 4, 6	2, 7, 10, 41, 53
3	1, 2, 3, 4, 7	2, 7, 10, 41, 54
4	1, 2, 3, 4, 8	2, 7, 10, 41, 55
5	1, 2, 3, 4, 9	2, 7, 10, 41, 56
6	1, 2, 3, 4, 10	2, 7, 10, 41, 57
7	1, 2, 3, 4, 11	2, 7, 10, 41, 58
8	1, 2, 3, 4, 12	2, 7, 10, 41, 59
9	1, 2, 3, 4, 13	2, 7, 10, 41, 60
10	1, 2, 3, 4, 14	2, 7, 10, 41, 61
Sheet2

I tried the formula you gave and it gives the #value error as well. I don't understand how the formulas I'm trying work for single digit values in column n but not for double digits.

 

[Fluff]

You're getting problems because you are trying to convert values like 1, to a number, how about

=SUMPRODUCT((LEFT($A$1:$A$10,LEN(N1))=N1&"")+(LEFT($B$1:$B$10,LEN(N1))=N1&""))

 

[michaelsmith559]

You're getting problems because you are trying to convert values like 1, to a number, how about

=SUMPRODUCT((LEFT($A$1:$A$10,LEN(N1))=N1&"")+(LEFT($B$1:$B$10,LEN(N1))=N1&""))

Thanks for the help. Works great!

 

[Fluff]

You're welcome & thanks for the feedback.