Multiple Countifs using sumproduct

[stroffso]

Hi All,

I have a workbook where I need one countif formula to incroporate multiple cells for one section. I have used adding the countifs together but it makes me workbook run super slowly.

I know there is a way of using some products for the same thing but dont know how to actually do it. Here is the formula im using, so you can see that just the Lists references needs to have 6 variables in cells A11-A16, could someone point me in the right direction of how to do this? Happy with any solution really that will speed things up

=COUNTIFS(Data!$G:$G,A8,Data!$U:$U,">="&Lists!$P$6,Data!$U:$U,"<="&Lists!$Q$6,Data!C:C,Lists!$A$11)+COUNTIFS(Data!$G:$G,A8,Data!$U:$U,">="&Lists!$P$6,Data!$U:$U,"<="&Lists!$Q$6,Data!C:C,Lists!$A$12)+COUNTIFS(Data!$G:$G,A8,Data!$U:$U,">="&Lists!$P$6,Data!$U:$U,"<="&Lists!$Q$6,Data!C:C,Lists!$A$13)+COUNTIFS(Data!$G:$G,A8,Data!$U:$U,">="&Lists!$P$6,Data!$U:$U,"<="&Lists!$Q$6,Data!C:C,Lists!$A$14)+COUNTIFS(Data!$G:$G,A8,Data!$U:$U,">="&Lists!$P$6,Data!$U:$U,"<="&Lists!$Q$6,Data!C:C,Lists!$A$15)+COUNTIFS(Data!$G:$G,A8,Data!$U:$U,">="&Lists!$P$6,Data!$U:$U,"<="&Lists!$Q$6,Data!C:C,Lists!$A$16)

thanks in advance

 

[stroffso]

Anyone got any ideas on this?

 

[Eric W]

You can shorten the formula to:

=SUMPRODUCT(COUNTIFS(Data!$G:$G,A8,Data!$U:$U,">="&Lists!$P$6,Data!$U:$U,"<="&Lists!$Q$6,Data!C:C,Lists!$A$11:$A$16))

but I doubt it will speed it up any. A specially written macro might be faster.

 

[stroffso]

Thanks Eric, I went with this and it worked fine. When you say specially written macro how would that work?

=SUM(COUNTIFS(Data!$F:$F,A8,Data!$U:$U,">="&Lists!$P$6,Data!$U:$U,"<="&Lists!$Q$6,Data!C:C,Lists!$A$11:Lists!$A$16))

You can shorten the formula to:

=SUMPRODUCT(COUNTIFS(Data!$G:$G,A8,Data!$U:$U,">="&Lists!$P$6,Data!$U:$U,"<="&Lists!$Q$6,Data!C:C,Lists!$A$11:$A$16))

but I doubt it will speed it up any. A specially written macro might be faster.

 

[Eric W]

This formula has to execute the COUNTIFS 6 times. A special UDF (User Defined Function) could be written that only reads the data 1 time, and processes it internally 1 time. I can't say for sure if it would run faster than the current version, but you can try it.

Open a copy of your workbook. Press Alt-F11 to open the VBA editor. Press Alt-I-M. Paste the following code into the window that opens:

Public Function MyCountIfs(r1 As Range, v1 As Variant, r2 As Range, v2 As Variant, _
                           r3 As Range, v3 As Variant, r4 As Range, v4 As Range)
Dim a1() As Variant, lr As Long, i As Long, j As Long, ctr As Long
Dim d1 As Variant, d2 As Variant, d3 As Variant, d4 As Variant, d4v As Variant


    ReDim a1(1 To v4.Cells.Count)
    lr = r1.Resize(1).Offset(Rows.Count - 1).End(xlUp).Row
    d1 = r1.Resize(lr).Value
    d2 = r2.Resize(lr).Value
    d3 = r3.Resize(lr).Value
    d4 = r4.Resize(lr).Value
    d4v = v4.Value
    For i = 1 To UBound(d4v)
        For j = 1 To UBound(d4v, 2)
            ctr = ctr + 1
            a1(ctr) = d4v(i, j)
        Next j
    Next i
    
    For i = 1 To UBound(d1)
        If d1(i, 1) = v1 Then
            If d2(i, 1) >= v2 Then
                If d3(i, 1) <= v3 Then
                    If UBound(Filter(a1, d4(i, 1), , vbTextCompare)) > -1 Then MyCountIfs = MyCountIfs + 1
                End If
            End If
        End If
    Next i
                    
End Function

Press Alt-Q to exit the editor. Now enter this formula:

=mycountifs(Data!$G:$G,A8,Data!$U:$U,Lists!$P$6,Data!$U:$U,Lists!$Q$6,Data!C:C,Lists!$A$11:$A$16)

This seems to work the same as the native Excel formula. It's as fast as I can make it, but I really can't say if it will be faster than the current formula. It would take a lot of test data to see.