Speed up sumproduct formula.

[motilulla]

Hello,

I am having problem using following formula in cell M6 =SUMPRODUCT(--($E$7:$E$86=K7),--($F$7:$F$86=L7),--($G$7:$G$86=M7)) to copied down to M59000 rows would there be any alternative solution for this problem to speed up please help.

*	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2
3
4
5				n1	n2	n3	n4	n5		P1	P2	P3	Count
6				1	2	5	15	42		1	2	3	0
7				1	3	6	18	24		1	4	12	1
8				1	4	12	16	48		1	6	12	1
9				1	6	12	18	42		4	7	9	0
10				6	10	16	39	46		10	12	14	0
11				6	10	19	29	36		6	12	20	0
12				6	10	20	29	37		6	10	30	1
13				6	10	21	45	49		20	26	35	1
14				6	10	23	44	49		23	24	27	1
15				6	10	28	45	50		20	21	22	1
16				6	10	30	41	45		25	26	41	1
17				6	10	31	36	39		23	26	29	1
18				20	24	27	37	39		26	36	37	1
19				20	25	34	42	45		9	12	15	0
20				20	26	35	36	42		15	16	18	0
21				20	27	30	36	43		4	5	6	0
22				20	27	34	46	50		25	26	29	1
23				20	27	41	43	50		27	28	29	2
24				23	24	26	34	50		23	25	32	1
25				23	24	27	37	47		18	27	39	1
26				6	22	24	25	36		27	28	29	2
27				6	22	26	29	48		35	36	37	0
28				18	27	39	43	47		3	4	5	0
29				18	28	39	46	48		7	8	9	0
30				19	26	35	45	49		12	13	14	0
31				19	32	35	37	44		17	18	59	0
32				19	36	38	42	46		27	32	45	0
33				20	21	22	31	45		31	32	38	1
34				20	21	26	32	46		20	24	26	0
35				20	21	27	33	40		5	10	15	0
36				25	26	29	49	50		12	13	14	0
37				25	26	41	44	47		27	32	46	0
38				25	27	31	35	43		12	14	16	0
39				27	28	29	34	43		20	27	41	1
40				27	28	31	46	47		46	47	48	0
41				27	29	37	39	49		15	16	17	0
42				27	31	33	42	50		23	37	38	0
43				28	29	39	48	50		22	27	32	0
44				28	29	40	46	50		8	9	10	0
45				23	25	32	37	42		11	12	13	0
46				23	26	29	37	40		14	15	16	0
47				23	26	32	41	45		18	19	20	0
48				20	28	35	42	43		19	20	21	0
49				20	37	39	44	50		23	24	25	1
50				23	24	25	31	44		27	28	31	2
51				23	24	26	33	42		27	31	37	0
52				26	34	37	39	49		27	31	33	2
53				26	36	37	38	42		29	30	32	1
54				27	28	29	34	43		6	10	19	1
55				27	28	31	46	47		37	42	45	0
56				27	29	37	39	49		24	27	32	0
57				27	31	33	42	50		35	37	42	0
58				28	29	39	48	50		5	9	12	0
59				28	29	40	46	50		7	15	22	0
60				28	30	42	45	49		9	12	15	0
61				29	30	32	34	46		35	36	37	0
62				29	33	39	41	44		1	22	27	0
63				31	32	38	40	42		5	6	12	0
64										1	2	49	0
65										23	26	29	1
66										12	13	14	0
67										42	43	44	0
68										45	46	47	0
69										15	19	20	0
70										45	49	50	0
71										19	26	35	1
72										6	10	28	1
73										27	28	29	2
74										27	28	31	2
75										28	29	39	2
76										35	37	42	0
77										5	9	12	0
78										11	12	13	0
79										14	15	16	0
80										18	19	20	0
81										12	13	14	0
82										20	27	34	1
83										32	34	46	0
84										37	38	42	0
85										39	48	50	0
86
87
88
89

For example the sample image is attached.

Thank you all.

I am using Excel 2000

Regards,
Moti

 

[Fluff]

That formula does not make sense, are you sure it's the correct formula?

 

[kvsrinivasamurthy]

Formula in cell M6 =SUMPRODUCT(--($E$7:$E$86=K7),--($F$7:$F$86=L7),--($G$7:$G$86=M7))
Alternate formula in cell M6
=COUNTIFS($E$7:$E$86,K7,$F$7:$F$86,L7,$G$7:$G$86,M7)

 

[Fluff]

@kvsrinivasamurthy maybe you missed this

I am using Excel 2000

and 2000 doesn't have countifs. Added to which the original formula is wrong.

 

[Marcelo Branco]

Try this macro

Sub aTest()
    Dim vData As Variant, vSearch As Variant, vResult As Variant
    Dim i As Long, j As Long, lCounter As Long
    
    vData = Range("D6:F" & Cells(Rows.Count, "D").End(xlUp).Row)
    vSearch = Range("J6:L" & Cells(Rows.Count, "J").End(xlUp).Row)
    vResult = Range("M6:M" & Cells(Rows.Count, "J").End(xlUp).Row)
    
    For i = 1 To UBound(vSearch, 1)
        lCounter = 0
        For j = 1 To UBound(vData, 1)
            If vData(j, 1) = vSearch(i, 1) Then
                If vData(j, 2) = vSearch(i, 2) Then
                    If vData(j, 3) = vSearch(i, 3) Then lCounter = lCounter + 1
                End If
            End If
        Next j
        vResult(i, 1) = lCounter
    Next i
    Range("M6:M" & Cells(Rows.Count, "J").End(xlUp).Row) = vResult
End Sub

Hope this helps

M.

 

[motilulla]

That formula does not make sense, are you sure it's the correct formula?

Sorry Fluff, You are correct I added wrong formula the below is the correct in cell M6 and copied down to M59000.

Thank you for the observation.

M6=SUMPRODUCT(--($D$6:$D$85=J6),--($E$6:$E$85=K6),--($F$6:$F$85=L6))

Kind Regards,
Moti

 

[Fluff]

Thanks for that, try Marcelo's macro, as I don't think you will get anything better than your formula for xl 2000

 

[motilulla]

Formula in cell M6 =SUMPRODUCT(--($E$7:$E$86=K7),--($F$7:$F$86=L7),--($G$7:$G$86=M7))
Alternate formula in cell M6
=COUNTIFS($E$7:$E$86,K7,$F$7:$F$86,L7,$G$7:$G$86,M7)

kvsrinivasamurthy, thank you for your help but as @Fluff said function countifs is not compatible with version 2000.

Kind Regards,
Moti

 

[motilulla]

Try this macro

Sub aTest()
    Dim vData As Variant, vSearch As Variant, vResult As Variant
    Dim i As Long, j As Long, lCounter As Long
   
    vData = Range("D6:F" & Cells(Rows.Count, "D").End(xlUp).Row)
    vSearch = Range("J6:L" & Cells(Rows.Count, "J").End(xlUp).Row)
    vResult = Range("M6:M" & Cells(Rows.Count, "J").End(xlUp).Row)
   
    For i = 1 To UBound(vSearch, 1)
        lCounter = 0
        For j = 1 To UBound(vData, 1)
            If vData(j, 1) = vSearch(i, 1) Then
                If vData(j, 2) = vSearch(i, 2) Then
                    If vData(j, 3) = vSearch(i, 3) Then lCounter = lCounter + 1
                End If
            End If
        Next j
        vResult(i, 1) = lCounter
    Next i
    Range("M6:M" & Cells(Rows.Count, "J").End(xlUp).Row) = vResult
End Sub

Hope this helps

M.

Marcelo Branco, your macro really is a time saver and a best solution for my version, ? also I am 100% agree with @Fluff as he said "Marcelo's macro, as I don't think you will get anything better than your formula for xl 2000"

I appreciate your kind help. Good Luck!

Kind Regards,
Moti

 

[Marcelo Branco]

You are welcome. Glad to help

M.