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

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

```
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