Sumproduct Syntax

[johnnyb5]

I researched past threads and found a sumproduct formula that I'm trying to adapt to my macro but I'm having terrible syntax issues. I need the sumproduct to meet five criteria then sum the remaining quantities in column M. My data is from columns A through X and about 600 rows but that does vary with each days download. I'm placing the formula in my summary table beginning in cell AA2 with the intent to drag down after I can get the syntax corrected. Any and all assistance is appreciated.

Range("AA2").Select
Activecell.Formula = "=SUMPRODUCT(--($A$1:$A & LstRw="=cells(0,-1) & "*"),--($C$1:$C & LstRw=xlFilterAutomaticFontColor),--($S$2:$S & LstRw="<>4EA and <>4EL"),--($T$2:$T & LstRw="E*"),--($W$2:$W & LstRw>"0"),$M$2:$M & LstRw)"

The first filter in column A references the ID code which is a 6 digit code in A and I'm identifying a four digit code in column Z "cells(0,-1)" with a wild card * character to select all of the six digit IDs in A that match the first 4 characters in column Z.
The second filter in column C is identifying black font (automatic font color) as unwanted products were previously identified by changing their font to red.
The third filter in column S is eliminating the product codes with 4EA and 4EL.
The forth filter in column T is identifying all product codes that start with E and includes the wild card *.
The fifth filter in column W is selecting all values greater in price than 0.
And finally in column M I'm trying to sum the remaining product quantities.

 

[shg]

You're not expecting that those asterisks will behave as wildcards, are you?

 

[johnnyb5]

Yes that was my intent. Is that incorrect as well??

 

[johnnyb5]

Perhaps the first filter should be re-written as:
Range("AA2").Select
Activecell.Formula = "=SUMPRODUCT(--(left($A$2:$A & LstRw, 4)="cells(0,-1)"),--
Would that work to match the first 4 characters in column A with the 4 digit ID code in Column Z (Formula is in column AA)?

 

[shg]

There are functions that accept wildcards, (AVERAGEIF(S), COUNTIF(S), SUMIF(S), HLOOKUP, VLOOKUP, MATCH, SEARCH), but not SUMPRODUCT.

I can't tell from your example what is supposed to be a VBA variable versus a named formula.