Forum Discussion
Solved
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...
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""}))")
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?
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?
- wsActive is the other sheet. The other sheet was given the name "Active" for active accounts.
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""}))")
- Yes, it worked.
Can you explain having ".address(external:=true" would make the code work? I never used this feature before.