How to use SUMPRODUCT in VBA

[Madiya]

Hi,
I have a large sheets containing 4700 rows of data with more the 700
unique code numbers.
I tried to use pivot table to get summary but it has limitations due to
which many code numbers are not displayed in the pivot tables.

Then, I tried to use sumproduct formula in cells but due to large
numbers of fumproduct formula, sheet became very slow.

So now I was trying to acheive the same results as of pivot table thru
VBA solution hoping that it might improve the performance.
Kindly let me know how to use the sumproduct formula in VBA.
My cell formula is as below.

=SUMPRODUCT(-(ALVXXL01!$B$2:$B$65536=A5),-(ALVXXL01!$K$2:$K$65536="MMV3"),(ALVXXL01!$P$2:$P$65536>0),ALVXXL01!$P$2:$P$65536)

If there is any better alternative, kindly let me know.

Thanks,
Madiya

 

[Guest]

Range("d1") =
Evaluate("SUMPRODUCT(--(ALVXXL01!$B$2:$B$65536=A5),--(ALVXXL01!$K$2:$K$65536=""MMV3""),--(ALVXXL01!$P$2:$P$65536>0),--(ALVXXL01!$P$2:$P$65536))")

 

[Madiya]

Thanks for your reply. It works great.
But this works only for one cell i.e. static. I need to have a dynamic
formula.
Let me explain.
I have 22 cloumns (fixed) in my data sheet. 2nd col is having mat codes
(may repeat in subsequent rows)
No of rows is variable but above 5000.


I have extracted and listed unique mat codes from data sheet to this
summary sheet in col A.
There are 4 variables (<0, >0, MMV3, MDA1)
I need to use this formula against each unique mat code in a new sheet
called Summary.
Col C is with MMV3 and <0
Col D is with MMV3 and >0
Col E is with MDA1 and <0
Col F is with MDA1 and >0

I can use vlookup or other regular formula in VBA but SUMPRODUCT is not
woking dynamic.

How can I do it?

Regards,
Madiya

 

[Guest]

Do mean something like:

I changed the A5 to A & r in the first part of the formula - I don't know if
this is correct. You can put similar formulas to the other columns but change
MMV3 to MDA1 and the ">" to "<"

For r = 2 to 100

Range("d" & r) =
Evaluate("SUMPRODUCT(--(ALVXXL01!$B$2:$B$65536=A" & r
&"),--(ALVXXL01!$K$2:$K$65536=""MMV3""),--(ALVXXL01!$P$2:$P$65536>0),--(ALVXXL01!$P$2:$P$65536))")

Next r

HTH

 

[Madiya]

Well, I have tried the same earlier and now again.
It gives error 2015.
Any other suggetion please....
Anyway thanks for your efforts to help me out.

Regards,
Madiya