Forum Discussion
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.
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""}))")
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""}))")
- 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?Is wsActive the active sheet or another sheet?
