Excel VBA custom function with sumif-like criteria functionality

Question

In Excel, I'm writing a custom function in VBA that needs to take a criteria string and criteria range like the built-in SUMIF function. Does Excel expose the functionality to test a criteria string anywhere in its API or do I have to write it myself?

In case it's relevant, I'm writing a "CountUniquesIf" formula, that counts the unique values in a range if they meet a criterion. This is what I have so far.

Function CountUniquesIf(CondRange As Range, Criteria As String, _
    Range As Range) As Long

    Static dict As New Scripting.Dictionary
    Dim index As Long

    index = 1
    For Each Cell In Range.Cells
        If CondRange(index).Value = Criteria And Cell.Value <> "" Then
            dict(Cell.Value) = Empty
        End If
        index = index + 1
    Next Cell

    CountUniquesIf = dict.Count
    dict.RemoveAll
End Function

Answer 1

I'm about to turn in and excel crashes on me since I switched to vista. But I'll look at this in my office tomorrow. You might succeed with a 'wrapper' on worksheetfunction.SumIf If that wont' work for you post it here. A few questions/suggestion though: even if it will work, using a variable name like 'Range' or 'Cell' can be confusing. It's not immediately apparent how you are keeping the unique values in dict while disposing of the duplicates. Would you be opposed to factoring this into 2 functions; one to return an array of values meeting the criteria, and another to count the unique members of that array? Have you tested cruder versions of your script? The 'CondRange(index)' syntax looks odd to me. Maybe it's fine and I just never used it (except on arrays and the like, not ranges). Why would you need to test for empty cells or zero-length strings if you already have a criteria? Is the criteria expressible as a regexp? ['Now you have 2 problems' hahaha..] Can you give examples of criteria and some dummy data? Did you try recording a Data\Filter\Advanced Filter menu sequence? It has a list range, criteria range, and unique values only option. There has to be a way to 'wrap' that in your function as long as it behaves well with your criteria. If your criteria isn't too complex, look at the help on the "Like" operator. Hopefully some of this is helpful to you. I see you are used to a more expressive language.

Answer 2

You can actually do the whole thing with just regular formulas if you want.

Please see:

http://www.officearticles.com/excel/count%5Funique%5Fvalues%5Fin%5Fmicrosoft%5Fexcel.htm

or

http://office.microsoft.com/en-us/excel/HP030561181033.aspx

You will need to modify the formula slightly, though, to cover the "if" part of your scenario:

=SUM(IF(FREQUENCY(IF((LEN(A1:A15)>0)*(B1:B15=D4),MATCH(A1:A15,A1:A15,0),""), IF((LEN(A1:A15)>0)*(B1:B15=D4),MATCH(A1:A15,A1:A15,0),""))>0,1))

Where A1:A15 is your Range, B1:B15 is your CondRange, and D4 is your Criterion.

Remember to enter this as an array formula (paste the formula and press Ctrl-Shift-Enter instead of just Enter).

That said, I think your VBA formula is a good solution too (probably more user-friendly than creating a monster array formula every time you need this type of count).

Update

Given your clarification, I really don't think there's a built-in "criterion analyzer", but I don't think it would be too difficult to enhance your formula to cover the different possible criteria. That way, your CountUniquesIf formula will really do what people think it does. Specifically, you could do a little parsing that checks for all the possible operators (is there anything besides "=", ">", ">=", "<", "<="?) that could be prefixed before the value.

Answer 3

Based on the clarifying comment, I think the easiest thing for you to do is to pass in an array of boolean values you get from using an array formula in the sheet, and then just test those.

That is, instead of passing in a range and a criterion, like b2:b15 and ">0", pass in the result of b2:b15>0, which will be an array or booleans. Then your test in your function can just be

If CondRange(index).Value And Cell.Value <> "" Then

and everything should work the way you want. Remember to enter the call to your UDF as an array formula.

It is possible to use Application.Evaluate with strings, but there are lots of limitations to doing it that way, and it seems like using an array formula to do your criteria test would be simpler in this case

Answer 4

Non-VBA way:

{=SUM(1/COUNTIF($A$2:$A$1001,$A$2:$A$1001)*(LEFT(A2:A1001)="C"))}

That will count uniques in A2:A1001 that start with "C"

For VBA, consider using the Evaluate method of the Application Object

If Asc(Left(Criteria, 1)) >= 60 And Asc(Left(Criteria, 1)) <= 62 Then
    bPass = Application.Evaluate(CondRange(Index).Value & Criteria)
Else
    bPass = CondRange(Index).Value = Criteria
End If