Forum Discussion

CTorre88's avatar
CTorre88
Copper Contributor
Aug 22, 2024

Trouble using WorksheetFunction SUM and COUNTIFS together

I'm trying to write a small vba where I need to check two conditions and if they the conditions are met add total the number of records that meet the conditions.  My code is as follows: 

 

wsActive.Range("D2").Value = Application.WorksheetFunction.SUM(WorksheetFunction.COUNTIFS(Range("K:K"), ">12", Range("I:I"), {""Open"",""Under Investigation"",""Acknowledge & Close""}))

 

wsTables.Range("D3").Value = Application.WorksheetFunction.SUM(Application.WorksheetFunction.CountIfs(Range("K:K"), ">=6", Range("K:K"), "<=12", Range("I:I"), {""Open"",""Under Investigation"",""Acknowledge & Close""}))

 

When I run the code, I get a message stating the braces, "{", are invalid characters.

When I remove the braces, then I get a message missing an object.

 

Can someone tell me what I am doing wrong?  I know I can use the functions in the cell, but I need it to be in a macro.  When I apply the function to the cell, it works; however, when I try to write it in Excel VBA, it fails.

  • CTorre88 

    How about

     

    wsActive.Range("D3").Value = Evaluate("=SUM(COUNTIFS(" & Range("K:K").Address(External:=True) & ", ""<6"", " & Range("I:I").Address(External:=True) & ", {""Open"", ""Under Investigation"", ""Acknowledge & Close""}))")

  • CTorre88 

    Using worksheet functions in VBA has limitations, as you have found. See if this works:

     

    wsActive.Range("D2").Value = Evaluate("SUM(COUNTIFS(K:K, "">=6"", K:K, ""<12"", I:I, {""Open"",""Under Investigation"",""Acknowledge & Close""}))")

    • CTorre88's avatar
      CTorre88
      Copper Contributor
      Hi Hans,

      No, is not working. Somehow my counts are all zeroes.

      Since I referencing two sheets, I changed the code to explicitly state the sheet the ranges belong to, which is as follows:

      wsActive.Range("D3").Value = Evaluate("SUM(COUNTIFS(" & ActiveSheet.Range("K:K") & ", ""<6""," & ActiveSheet.Range("I:I") & ", {""Open"", ""Under Investigation"", ""Acknowledge & Close""}))"

      I now get a message stating: Run-time error '13': Type mismatch.
      I do not know why I'm getting this message.

      Is my syntax incorrect?

Resources