Application.Evaluate("SUMPRODUCT with COUNTIF")

[RunTime91]

Hello -

I'm trying to get the long-hand version of the below formula to work in my VBA - The formula below works perfectly.
[SUMPRODUCT((D5:D500<>"")/COUNTIF(D5:D500,D5:D500&""))]

I've tried several iterations of the following but I either return '0' or 'Error2015' - The formula below returns zero
Application.Evaluate("SUMPRODUCT((D5:D500<>"")/COUNTIF(D5:D500,D5:D500&""))")

Thanks for any assistance helping me understand what I'm doing wrong

Rt91

 

[Fluff]

You need to double-up on the quotes inside the formula.

 

[Osvaldo Palmeiro]

Hi.
You should double the double quotes.

[A1] = Evaluate("SUMPRODUCT((D5:D500<>"""")/COUNTIF(D5:D500,D5:D500&""""))")

 

[Norie]

You need to double up the quotes in the formula.

Evaluate("SUMPRODUCT((D5:D500<>"""")/COUNTIF(D5:D500,D5:D500&""""))")

 

[RunTime91]

I would say that's a consensus
Thanks everyone!
Works Perfectly

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[RunTime91]

I hope this is okay to piggy back on this thread

I am now trying to use a LastRow variable in this formula and...Well, here I am...

Below is the last iteration before giving up there have been many attempts & Google searches.

These work:

TtlCases = [SUMPRODUCT((C2:C500<>"")/COUNTIF(C2:C500,C2:C500&""))]
TtlCases = Application.Evaluate("SUMPRODUCT((C5:C500<>"""")/COUNTIF(C5:C500,C5:C500&""""))")

This...Not so much

Lr = Cells(Rows.Count, 3).End(xlUp).Row
TtlCases = [SUMPRODUCT((C2:C"Lr"<>"")/COUNTIF(C2:C"Lr",C2:C"Lr"&""))]

I've played with the ampersand, single quotes, no quotations,

I did read in one of my searches that using variables in the Evaluate formula is not allowed - Then I found what looked like a variable be used - But the method didn't work for me

Thanks everyone for any help

Rt91

 

[Fluff]

You cannot use the short form of Evaluate with variables.
It would need to be like

Evaluate("SUMPRODUCT((C2:C" & Lr & "<>"""")/COUNTIF(C2:C" & Lr & ",C2:C" & Lr & "&""""))")

 

[RunTime91]

She's a winner!

Thanks Fluff!!

I'm not crazy about the short form as it is - My experience from back in the Excel 2003 days is it can be a little volatile/finicky

Thank You again, Sir

 

[Fluff]

Glad to help & thanks for the feedback.