title: Applicatoin.SumProduct/Countifs in VBA
body: <p>I'm trying to use the Application SumProduct &amp; Countifs in vba rather than the .Formula method. Below is an example of the report I run every month:</p>
  
  <p><a href="https://i.stack.imgur.com/JerEI.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/JerEI.png" alt="enter image description here"></a></p>
  
  <p>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:</p>
  
  <p><a href="https://i.stack.imgur.com/x7cfh.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/x7cfh.png" alt="enter image description here"></a></p>
  
  <p>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:</p>
  
  <p>Adjuster Home Office Name>Data Set (Column C from the report)>Claim Type (Column E from the report)>State (Column G from the report)</p>
  
  <p>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:</p>
  
  <pre><code>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") &amp; " Claims"
  priorclaimstab = Sheet2.Range("C3") &amp; " 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" &amp; Rows.Count).End(xlUp).Row
  
  Set linerngs = Sheet2.Range("D12:D" &amp; 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 &amp; ":" &amp; lasttype)
  StateJuris = Sheet2.Range(firststate &amp; ":" &amp; laststate)
  
      If InStr(1, lineitem.Value, "Indemnity") &gt; 0 And InStr(1, lineitem.Value, "AOS") = 0 Then
  
          lineitem.Offset(0, 16) = Application.WorksheetFunction.SumProduct(wsf.CountIfs(Sheets(claimstab).Range("B2:B" &amp; Tab1LastRow), HomeOffice, Sheets(claimstab).Range("C2:C" &amp; Tab1LastRow), DataSet, Sheets(claimstab).Range("E2:E" &amp; Tab1LastRow), ClaimTypes, Sheets(claimstab).Range("G2:G" &amp; Tab1LastRow), StateJuris))
  
      End If
  
  Next lineitem
  </code></pre>
  
  <p>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" .</p>
  
  <p>I cant figure out how to make this work with the VBA Application version of countifs &amp; sumproduct.  any help on this would be appreciated. </p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>In order to not count what states are in range E5:W5 in the second image i posted, i tried something like this:</p>
  
  <pre><code>        If InStr(1, lineitem.Value, "Indemnity") &gt; 0 And InStr(1, lineitem.Value, "AOS") &gt; 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" &amp; Tab1LastRow), HomeOffice, Sheets(claimstab).Range("C2:C" &amp; Tab1LastRow), DataSet, Sheets(claimstab).Range("E2:E" &amp; Tab1LastRow), ClaimType.Value, Sheets(claimstab).Range("G2:G" &amp; Tab1LastRow), "&lt;&gt;" &amp; DedState.Value))
              Next DedState
          Next ClaimType
  
          lineitem.Offset(0, 16) = c
          End If
  </code></pre>
  
  <p>however, this isn't working and i didn't think that i would.  I'm assuming i need to do something like:</p>
  
  <pre><code>If InStr(1, lineitem.Value, "Indemnity") &gt; 0 And InStr(1, lineitem.Value, "AOS") &gt; 0 Then
  
      c = 0
      nc = 0
  
  For Each ClaimType In ClaimTypes
  
  c = c + Application.WorksheetFunction.SumProduct(wsf.CountIfs(Sheets(claimstab).Range("B2:B" &amp; Tab1LastRow), HomeOffice, Sheets(claimstab).Range("C2:C" &amp; Tab1LastRow), DataSet, Sheets(claimstab).Range("E2:E" &amp; 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" &amp; Tab1LastRow), HomeOffice, Sheets(claimstab).Range("C2:C" &amp; Tab1LastRow), DataSet, Sheets(claimstab).Range("E2:E" &amp; Tab1LastRow), ClaimType.Value, Sheets(claimstab).Range("G2:G" &amp; Tab1LastRow), DedState.Value))
  
      Next DedState
  
  Next ClaimType
  
      lineitem.Offset(0, 16) = c - nc
  
  End If
  </code></pre>