User Defined Function (UDF) - USING WILDCARDS IN SUMPRODUCT

dsr123 · Oct 25th 2010

Hello

I'm trying to write a UDF that will get round a problem with SUMPRODUCT. Basically, I have a large DataTable with about 180 columns of data in it. I need to be able to work out the number of entries in the table that match certain criteria (stored in a range called Criteria). In principle, the number of criteria could equal the number of columns, and I'm working on that basis in what follows.

In other words, I could use COUNTIFS, but I can't guarantee that all users will have Excel 2007 or newer, and I've read that the max number of criteria for COUNTIFS is 157 (<180).

So I'm therefore having to go for a SUMPRODUCT formula along the lines of

Code

=SUMPRODUCT(--(D4:D11=G4),--(E4:E11=H4),--(F4:F11=I4))

Where in this test example, the DataTable range is D4:F11 and the Criteria range is G4:I4.

The problem is that I can't use wildcards in the above. So if I want to set I4 = "*" to mean all of column F4:F11, then I can do this using COUNTIFS, but not SUMPRODUCT.

I'm therefore trying to write a UDF to produce the SUMPRODUCT formula automatically, so that if an entry in the criteria column is "*" or "", then it skips that column in the DataSheet/Criteria ranges and moves on to the next. I've come up with the code below and it comes up with a big juicy #VALUE! error when I try.

The UDF has been declared in the VBA module (not the sheet), so that's not the problem. Any suggestions for what I'm doing wrong?

Many thanks in advance,
Darren

Code

Function DSRFUNCTION(DataTable As Range, Criteria As Range)


Dim i As Integer


Dim FStr As String, TempStr As String


Dim DataTableTL As Range
Dim CriteriaTL As Range


If DataTable.Columns.Count <> Criteria.Columns.Count Or Criteria.Rows.Count > 1 Then
MsgBox "Incompatible Data - Please Check Formula"
FStr = ""
Else


    With DataTable
        DataTableTL = Cells(.Row, .Column).Address(0, 0)
    End With


    With Criteria
        CriteriaTL = Cells(.Row, .Column).Address(0, 0)
    End With


' Rem aiming for formula along the lines of =SUMPRODUCT(--(D4:D11=G4),--(E4:E11=H4),--(F4:F11=I4))


FStr = "=SUMPRODUCT("


For i = 0 To Criteria.Columns.Count - 1


    If CriteriaTL.Offset(0, i).Value <> "" Or CriteriaTL.Offset(0, i).Value <> "*" Then
        'Add additional entry in formula
        TempStr = "--(" & DataTableTL.Offset(0, i).Address & ":" & DataTableTL.Offset(DataTable.Rows.Count - 1, i).Address _
        & "=" & CriteriaTL.Offset(0, i).Address & "),"
        FStr = FStr & TempStr
    End If
    'Otherwise leave formula alone
Next i


'Delete last comma in formula then close brackets
FStr = Left(FStr, Len(FStr) - 1)
FStr = FStr & ")"


End If


DSRFUNCTION = FStr




End Function

Display More

**StephenR** · Oct 25th 2010

Re: User Defined Function (UDF) - USING WILDCARDS IN SUMPRODUCT

Have you considered Advanced Filter?

**Krishnakumar** · Oct 25th 2010

Re: User Defined Function (UDF) - USING WILDCARDS IN SUMPRODUCT

Hi,

made couple of changes

Code

Function DSRFUNCTION(DataTable As Range, Criteria As Range)


Dim i As Integer


Dim FStr As String, TempStr As String


Dim DataTableTL As String
Dim CriteriaTL As String


If DataTable.Columns.Count <> Criteria.Columns.Count Or Criteria.Rows.Count > 1 Then
MsgBox "Incompatible Data - Please Check Formula"
FStr = ""
Else


    With DataTable
        DataTableTL = Cells(.Row, .Column).Address(0, 0)
    End With


    With Criteria
        CriteriaTL = Cells(.Row, .Column).Address(0, 0)
    End With


' Rem aiming for formula along the lines of =SUMPRODUCT(--(D4:D11=G4),--(E4:E11=H4),--(F4:F11=I4))


FStr = "=SUMPRODUCT("


For i = 0 To Criteria.Columns.Count - 1


    If Range(CriteriaTL).Offset(0, i).Value <> "" Or Range(CriteriaTL).Offset(0, i).Value <> "*" Then
        'Add additional entry in formula
        TempStr = "--(" & Range(DataTableTL).Offset(0, i).Address & ":" & Range(DataTableTL).Offset(DataTable.Rows.Count - 1, i).Address _
        & "=" & Range(CriteriaTL).Offset(0, i).Address & "),"
        FStr = FStr & TempStr
    End If
    'Otherwise leave formula alone
Next i


'Delete last comma in formula then close brackets
FStr = Left(FStr, Len(FStr) - 1)
FStr = FStr & ")"


End If


DSRFUNCTION = Evaluate(FStr)




End Function

Display More

HTH

Rob Xaos · Oct 25th 2010

Re: User Defined Function (UDF) - USING WILDCARDS IN SUMPRODUCT

I would agree with Stephen that Advanced Filter would be the way to go. It has all the capabilities of COUNTIF(S) criteria and more since you can create AND/OR functions. You might want to use some VBA to build the criteria but the performance of the filter will be much faster than a UDF.

**mikerickson** · Oct 25th 2010

Re: User Defined Function (UDF) - USING WILDCARDS IN SUMPRODUCT

Have you looked at the DCOUNT function?

daddylonglegs · Oct 25th 2010

Re: User Defined Function (UDF) - USING WILDCARDS IN SUMPRODUCT

You can do this with a SUMPRODUCT formula. Might not be the best approach for you but this version of your formula will treay blanks or * in the criteria range as an "any value" criteria

=SUMPRODUCT(--(D4:D11=IF(OR(G4={"","*"}),D4:D11,G4)),--(E4:E11=IF(OR(H4={"","*"}),E4:E11,H4)),--(F4:F11=IF(OR(I4={"","*"}),F4:F11,I4)))

Note that SUMPRODUCT lets you have up to 255 arrays in Excel 2007 (but it's limited to 30 in Excel 2003)

COUNTIFS, btw has a limit of 127 range/criteria pairs

dsr123 · Oct 25th 2010

Re: User Defined Function (UDF) - USING WILDCARDS IN SUMPRODUCT

Guys

HUGE thanks for all your help and suggestions.

I tried it both with an Advanced Filter and with the modified UDF suggested by daddylonglegs (and code as modified by Krishna). I've got both up and running and will do some speed testing to see which works best when used for real.

In case anybody else wants to use it, here's the final version of the code for the UDF:

Regards - and thanks, once again.
Darren

Code

Function DSRFUNCTION(DataTable As Range, Criteria As Range)


Dim i As Integer


Dim FStr As String, TempStr As String


Dim DataTableTL As String
Dim CriteriaTL As String


If DataTable.Columns.Count <> Criteria.Columns.Count Or Criteria.Rows.Count > 1 Then
MsgBox "Incompatible Data - Please Check Formula"
FStr = ""
Else


    With DataTable
        DataTableTL = Cells(.Row, .Column).Address(0, 0)
    End With


    With Criteria
        CriteriaTL = Cells(.Row, .Column).Address(0, 0)
    End With


' Rem aiming for formula along the lines of
' =SUMPRODUCT(--(D4:D11=IF(OR(G4={"","*"}),D4:D11,G4)),--(E4:E11=IF(OR(H4={"","*"}),E4:E11,H4)),--(F4:F11=IF(OR(I4={"","*"}),F4:F11,I4)))


FStr = "=SUMPRODUCT("


For i = 0 To Criteria.Columns.Count - 1


        TempStr = "--(" & _
        Range(DataTableTL).Offset(0, i).Address & ":" & Range(DataTableTL).Offset(DataTable.Rows.Count - 1, i).Address _
       & "=IF(OR(" _
       & Range(CriteriaTL).Offset(0, i).Address _
       & "={" & Chr(34) & "" & Chr(34) & "," & Chr(34) & "*" & Chr(34) & "})," _
       & Range(DataTableTL).Offset(0, i).Address & ":" & Range(DataTableTL).Offset(DataTable.Rows.Count - 1, i).Address & "," _
       & Range(CriteriaTL).Offset(0, i).Address _
       & ")),"
           
        FStr = FStr & TempStr
    
Next i


'Delete last comma in formula then close brackets
FStr = Left(FStr, Len(FStr) - 1)
FStr = FStr & ")"


End If




DSRFUNCTION = Evaluate(FStr)




End Function

Display More

Rob Xaos · Oct 26th 2010

Re: User Defined Function (UDF) - USING WILDCARDS IN SUMPRODUCT

Thank you for supplying your solution.

User Defined Function (UDF) - USING WILDCARDS IN SUMPRODUCT

Participate now!

Share