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""}))")
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.CTorre88 Range("...").Address(External:=True) returns the address of the range including the workbook and worksheet name, so that you refer to the correct range regardless of where it is.
- I only use the .Address property. I guess from now onward, I will be using the .Address(external:=true) property.
Thank you for your assistance.