Application.Evaluate("SUMPRODUCT with COUNTIF")

RunTime91

Active Member
Joined
Aug 30, 2012
Messages
262
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
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,324
Office Version
  1. 365
Platform
  1. Windows
You need to double-up on the quotes inside the formula.
 
Solution

Osvaldo Palmeiro

Well-known Member
Joined
Feb 24, 2009
Messages
610
Office Version
  1. 365
Platform
  1. Windows
Hi.
You should double the double quotes.
Rich (BB code):
[A1] = Evaluate("SUMPRODUCT((D5:D500<>"""")/COUNTIF(D5:D500,D5:D500&""""))")
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,234
Office Version
  1. 365
Platform
  1. Windows
You need to double up the quotes in the formula.
VBA Code:
Evaluate("SUMPRODUCT((D5:D500<>"""")/COUNTIF(D5:D500,D5:D500&""""))")
 

RunTime91

Active Member
Joined
Aug 30, 2012
Messages
262

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,324
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

RunTime91

Active Member
Joined
Aug 30, 2012
Messages
262

ADVERTISEMENT

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:
Code:
TtlCases = [SUMPRODUCT((C2:C500<>"")/COUNTIF(C2:C500,C2:C500&""))]
TtlCases = Application.Evaluate("SUMPRODUCT((C5:C500<>"""")/COUNTIF(C5:C500,C5:C500&""""))")

This...Not so much
Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,324
Office Version
  1. 365
Platform
  1. Windows
You cannot use the short form of Evaluate with variables.
It would need to be like
VBA Code:
Evaluate("SUMPRODUCT((C2:C" & Lr & "<>"""")/COUNTIF(C2:C" & Lr & ",C2:C" & Lr & "&""""))")
 

RunTime91

Active Member
Joined
Aug 30, 2012
Messages
262
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,324
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,426
Messages
5,601,595
Members
414,462
Latest member
StageRiis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top