Hi,I am attempting to count the number of unique string variables in a list (for example, let's say NFL team names). I found this formula that works just fine in excel...=SUMPRODUCT((A2:A100 <> "")/COUNTIF(A2:A100,A2:A100 & ""))I am now attempting to put this logic into vba via...=APPLICATION.WORKSHEETFUNCTION.SUMPRODUCT((A2:A100 <> "")/APPLICATION.WORKSHEETFUNCTION.COUNTIF(A2:A100,A2:A100 & "")). I get the following error message...Compile error: Type Mismatch and the <> is highlighted.I think I must be dimensioning the variables (the list of team names is the most likely candidate) incorrectly, but I have tried string, variant, and several others and I can't get rid of the error message.I'm sure I'm doing something stupid. Can someone help me out?Thanks,Beachcomber

[SUMPRODUCT(($A$2:$A$100<>"")/ COUNTIF($A$2:$A$100, $A$2:$A$100 & ""))]

Thank you for the help, but I cannot get the formula to work even with your advice.I may have to try something different.Beachcomber

It works here. See the attached file.But maybe I don't get what you want to do.

QuoteOriginally posted by: tagomaIt works here. See the attached file.But maybe I don't get what you want to do.That works, and is probably more or less what OP called for, but it is doubly ugly. Not only does it call specific cell addresses from VBA (which breaks the Excel recalculation logic and is not robust to changes in the sheet layout), but you do it inside a function, thus breaking somebody's rule that functions should only take arguments that are explicitly passed to them. I am interested in understanding why it works, since there is some feature of VBA at work here that I was not aware of.

Thanks. That works. The difference is that I used APPLICATION.WORKSHEETFUNCTION.SUMPRODUCT instead of SUMPRODUCT.The fixed cell range was only for demonstration purposes. I am defining and populating a variable to work with inside VBA.I appreciate the help.Beachcomber