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