Applicatoin.SumProduct/Countifs in VBA

Question

I'm trying to use the Application SumProduct & Countifs in vba rather than the .Formula method. Below is an example of the report I run every month:

I need to be able to organize this data by multiple criteria. Below is a picture of the spreadsheet where I'm attempting to organize this data by the given criteria:

I've created macros so the user of this spreadsheet enters the criteria on one tab, and then this tab is automatically formatted based on their input. The data needs to be organized as follows:

Adjuster Home Office Name>Data Set (Column C from the report)>Claim Type (Column E from the report)>State (Column G from the report)

after making several attempts at this, I know that the countifs within sumproduct formula will achieve this. Below is a snippet of the code I've written so far:

Dim linerngs As Range
Dim lineitem As Range
Dim lastlinerow As Long
Dim Tab1LastRow As Long
Dim claimstab As String

Dim wsf
Dim SpecialStates As Range
Dim DedicatedUnits As Range
Dim HomeOffice As String
Dim DataSet As String

claimstab = Sheet2.Range("C2") & " Claims"
priorclaimstab = Sheet2.Range("C3") & " Claims"

Tab1LastRow = Sheets(claimstab).Cells(Sheets(claimstab).Rows.Count, "A").End(xlUp).Row


Set wsf = Application.WorksheetFunction

Set SpecialStates = Sheet2.Range("E2:AA2")
Set DedicatedUnits = Sheet2.Range("E5:BB5")

lastlinerow = Sheet2.Range("D" & Rows.Count).End(xlUp).Row

Set linerngs = Sheet2.Range("D12:D" & lastlinerow).SpecialCells(xlCellTypeConstants, 23)

    For Each lineitem In linerngs

HomeOffice = lineitem.Offset(0, -3).Value
DataSet = lineitem.Offset(0, -1).Value

firsttype = lineitem.Offset(0, 1).Address(False, False)
lasttype = lineitem.Offset(0, 6).Address(False, False)

firststate = lineitem.Offset(0, 7).Address(False, False)
laststate = lineitem.Offset(0, 15).Address(False, False)

ClaimTypes = Sheet2.Range(firsttype & ":" & lasttype)
StateJuris = Sheet2.Range(firststate & ":" & laststate)

    If InStr(1, lineitem.Value, "Indemnity") > 0 And InStr(1, lineitem.Value, "AOS") = 0 Then

        lineitem.Offset(0, 16) = Application.WorksheetFunction.SumProduct(wsf.CountIfs(Sheets(claimstab).Range("B2:B" & Tab1LastRow), HomeOffice, Sheets(claimstab).Range("C2:C" & Tab1LastRow), DataSet, Sheets(claimstab).Range("E2:E" & Tab1LastRow), ClaimTypes, Sheets(claimstab).Range("G2:G" & Tab1LastRow), StateJuris))

    End If

Next lineitem

This is returning me a value of zero for everything. I started poking around and found that if I replace the variable "ClaimTypes" with "IN" and replace the variable "StateJuris" with "TX" or any other state abbreviation within the Countifs code, it gives me a correct calculation. I know in the spreadsheet formula version of countifs, you can reference a horizontal line of cells and evaluate them at once. For instance, if you look at row 12 of the second image, i'd need to be able to evaluate Column G of the report in the first image for the states "AK";"CA";"HI";"TX" .

I cant figure out how to make this work with the VBA Application version of countifs & sumproduct. any help on this would be appreciated.

EDIT:

In order to not count what states are in range E5:W5 in the second image i posted, i tried something like this:

        If InStr(1, lineitem.Value, "Indemnity") > 0 And InStr(1, lineitem.Value, "AOS") > 0 Then
        c = 0
        For Each ClaimType In ClaimTypes
            For Each DedState In DedicatedUnits
            c = c + Application.WorksheetFunction.SumProduct(wsf.CountIfs(Sheets(claimstab).Range("B2:B" & Tab1LastRow), HomeOffice, Sheets(claimstab).Range("C2:C" & Tab1LastRow), DataSet, Sheets(claimstab).Range("E2:E" & Tab1LastRow), ClaimType.Value, Sheets(claimstab).Range("G2:G" & Tab1LastRow), "<>" & DedState.Value))
            Next DedState
        Next ClaimType

        lineitem.Offset(0, 16) = c
        End If

however, this isn't working and i didn't think that i would. I'm assuming i need to do something like:

If InStr(1, lineitem.Value, "Indemnity") > 0 And InStr(1, lineitem.Value, "AOS") > 0 Then

    c = 0
    nc = 0

For Each ClaimType In ClaimTypes

c = c + Application.WorksheetFunction.SumProduct(wsf.CountIfs(Sheets(claimstab).Range("B2:B" & Tab1LastRow), HomeOffice, Sheets(claimstab).Range("C2:C" & Tab1LastRow), DataSet, Sheets(claimstab).Range("E2:E" & Tab1LastRow), ClaimType.Value))

Next ClaimType


For Each ClaimType In ClaimTypes

    For Each DedState In DedicatedUnits

            nc = nc + Application.WorksheetFunction.SumProduct(wsf.CountIfs(Sheets(claimstab).Range("B2:B" & Tab1LastRow), HomeOffice, Sheets(claimstab).Range("C2:C" & Tab1LastRow), DataSet, Sheets(claimstab).Range("E2:E" & Tab1LastRow), ClaimType.Value, Sheets(claimstab).Range("G2:G" & Tab1LastRow), DedState.Value))

    Next DedState

Next ClaimType

    lineitem.Offset(0, 16) = c - nc

End If

I apologize, i guess i wasn't clear enough. im using countifs within a sumproduct formula to calculate based on all of the different criteria. — Graham Chandler
sorry, that makes no sense to me. COUNTIFS returns a scalar. SUMPRODUCT on a scalar returns the scalar. The SUMPRODUCT isn't doing anything here... What exactly do you want to return? It's not clear from your question. — dashnick
@GrahamChandler Based on your comment to the answer below, it seems you are in the habit of relying on Excel Functions within your VBA applications - don't do this. You are severely limiting the power available to you through VBA, and you are painting yourself into corners that wouldn't exist if you had a wider toolset available. — Grade 'Eh' Bacon
Like Batman in The Dark Knight Rises, you won't be able to fully commit to learning non-Excel-Function tools unless you take them away from yourself. For every Excel Function tool you have, there's probably a better VBA option available [every once in a while a shortcut through Excel Functions is handy, but you won't even know when it's worth it and when it isn't, if you're always relying on them]. — Grade 'Eh' Bacon

dashnick dashnick · Accepted Answer · 2016-02-01T19:21:33

First of all, you can just use SUM() here instead of SUMPRODUCT() - you are only supplying one argument to SUMPRODUCT(), so it is only returning the sum anyway.

Secondly, if you really wanted to do an array formula in VBA, wrap the entire formula in EVALUATE([formula]). But this won't work for you because...

Thirdly, even if you entered this as an array formula in an excel worksheet, it would not work correctly if there was multiple criteria for both claim types and states, as it would only add the number of occurrences of claim1 and state1 and claim2 and state2 etc. (If either column only has one, then the formula would work, however.)

So, if you want to do this, what I would do is a nested FOR loop, where for each Claim Type-State combination, you do another COUNTIFS() using that particular Claim Type and State and increment a running total.

Update: For example, something like

c = 0
for each claimType in claimTypes
    for each state in StateJuris
        c = c + wsf.CountIfs(Sheets(claimstab).Range("B2:B" & Tab1LastRow), HomeOffice, Sheets(claimstab).Range("C2:C" & Tab1LastRow), DataSet, Sheets(claimstab).Range("E2:E" & Tab1LastRow), ClaimType.value, Sheets(claimstab).Range("G2:G" & Tab1LastRow), State.value)
    next state
next claimType

lineitem.Offset(0, 16) = c

Applicatoin.SumProduct/Countifs in VBA

1 Answers