SUMIFS in VBA

[Euler271]

I have an Excel userform wherein the user selects a member number from a combo box, a state from a second combo box and one or multiple counties from a list box. I then have a "Calculate" button that I want to sum the number of members in the selected state and countiesr. There can be just one state but multiple counties selected. I'm more of an expert in Access in which I'd use the DSUM function in VBA. In Excel, I'm guessing I will need to use the SumIfS function along with the wrapping SUM function.

My problem is creating the string for the county criteria. I've tried using the SPLIT function to create an array from the string but that doesn't work. When I manually enter a county name such as "Pima" into the function, it works but when I try to manufacture the string with VBA it doesn't work. Here's the formula I'm using:

Me!txtMembers = Application.WorksheetFunction.Sum(Application.WorksheetFunction.SumIfs(Range("Members"), Range("ST"), Me.Controls("cboState").Value, Range("County"), strCounties))

When the user presses the "Calculate" button a string is created by looping through the county list box. I need to put something in Criteria2 automatically but I can't seem to find the secret sauce. Access is much easier but, in this case, I need to use Excel because of its better pivot tables.

 

[FormR]

Hi, you could loop through the selected countries and do a SUMIFS() for each one accumulating the sum as you go.

 

[Euler271]

Great idea. I should have thought of it myself. I'll try it immediately. Thank you very much.

 

[Jonmo1]

I don't think the Sum(Sumifs syntax will work when written in VBA using Worksheetfunction.xxx
It doesn't process as an array like it does in a sheet formula.

You can try using Evaluate, and write the formula the same as you would in a cell formula, with a little concatenation

Me!txtMembers = Evaluate("=SUM(SUMIFS(Members,ST," & Me.Controls("cboState").Value & ",County," & strCounties &"))"

strCounties would need to be built as a string like
strCounties = "{""ValueA"",""ValueB""}"

 

[Euler271]

Thank you very much for your input. I wasn't aware of the EVALUATE function.