SUMPRODUCT working in worksheet but not in VBA - please help!

[nick_clarke]

I've developed a sumproduct function that works perfectly when ran within my worksheet, however I am receiving a type 13 mismatch error when attempting to use this via VBA. My code is as follows:

dim t as integer

t = Evaluate("=SUMPRODUCT(('Sheet 1'!$W$2:$W$15994>=B19)*(('Sheet 1!$W$2:$W$15994<=B20))*('Sheet 1'!$V$2:$V$15994="M - filed as complete")")

txttest1.Value = t

I'm sure it is something to do with the "M - filed as complete" within the final criteria section, but I am not sure what the problem is.

I've been searching the internet for hours trying to find a solution and I would be really grateful if someone could please help me.

Thanks

 

[eloirjr]

Great, thanks.
Last question.
I have to do this calculation in more 3 column, good. I´m receiving error 2010 on the 3th column, ok, I´ll check later, but, when the error happen the evaluate only shows me the first result, like, something keep in cache, this behavior is correct?
Thanks

 

[Andrew Poulsom]

What's error 2010?

 

[eloirjr]

Hi, Based on my test this errors means arrays with different size, I just like to clean the error to not impact the evalute on the next loop
Thanks

 

[Andrew Poulsom]

You will need to give me a bit more information. What exactly is causing the error?

 

[eloirjr]

Hi, I have fixed the error, I just observe the when this error happened, the other values on the loop kept always the same.
thanks a lot for your help.
Have a nice week

 

[eloirjr]

Hello,
It´s me again, After your help to sum the columns based month and year, I need just count, the formula that you help me is :TotParcPrev1 = Evaluate("=SUMPRODUCT((TEXT(Venda!$L$3:$L$" & Lastcl & ",""mmmyyyy"")=TEXT(A" & p + 8 & ",""mmmyyyy""))*Venda!$m$3:$m$" & Lastcl & ")")
How Can I just countif?
Thanks

 

[Andrew Poulsom]

I think that would be:

TotParcPrev1 = Evaluate("=SUMPRODUCT(--(TEXT(Venda!$L$3:$L$" & Lastcl & ",""mmmyyyy"")=TEXT(A" & p + 8 & ",""mmmyyyy"")))")

 

[eloirjr]

Perfect, thanks again
you´re the best