Trying to sumifs with multiple criteria in the same column

**Rokn** · 07-22-2016, 07:14 PM

Alright, so here goes. I have a front end formula that I'm trying to turn into vba, but I can't seem to get it to work the same way, or at all for that matter.

I have a sheet with a list of industries in column "A." Next to them, in column "B" are a list of values, we'll say $ amounts.

So, the formula I created that seemed to work was:

=sum(sumif(B:B,A:A,{"BusinessServices", "Healthcare"}))

So, I simply tried replicating it in vba with:

=application.sum(application.sumif(sheet1.range("B:B","A:A",{var1, var2})))

where the 'vars' represent one of the above values . . . and it errors out because the curly brace isn't a recognized symbol in the excel vbe.

Any assistance you can provide me in finding a workaround for this seemingly rather simple problem is greatly appreciated.

Thank you in advance for considering helping.

I would attach a workbook, but I only have excel 2003 at home and I'm using 10 at work . . . also sorry in advance

**AlphaFrog** · 07-22-2016, 08:34 PM

The syntax for the worksheet formula would be...

Formula:

=sum(sumif(A:A,{"BusinessServices", "Healthcare"},B:B))

The VBA equivalent could be...

x = Evaluate("SUM(SUMIF(A:A,{""" & var1 & """,""" & var2 & """},B:B))")

Or...

y = Application.Sum(Application.SumIf(Sheet1.Range("A:A"), Array(var1, var2), Sheet1.Range("B:B")))

Or...

    Dim MyArray As Variant
    MyArray = Array("BusinessServices", "Healthcare")
    z = Application.Sum(Application.SumIf(Sheet1.Range("A:A"), MyArray, Sheet1.Range("B:B")))

**Rokn** · 07-22-2016, 08:57 PM

Awesome, figured it would be an easy enough solution. Thank you, thank you, thank you!!!! Saved ME!!!

**AlphaFrog** · 07-22-2016, 09:48 PM

You're welcome. Thanks for the feedback.

Trying to sumifs with multiple criteria in the same column

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Trying to sumifs with multiple criteria in the same column

Re: Trying to sumifs with multiple criteria in the same column

Re: Trying to sumifs with multiple criteria in the same column

Re: Trying to sumifs with multiple criteria in the same column

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] SUMIFS Multiple Criteria Same Column

[SOLVED] SUMIFS with text wildcard and multiple criteria in the same column

[SOLVED] Sumifs to check multiple criteria in the same column

[SOLVED] SUMIFS w/ multiple criteria in a single column (using references to cells, not values)

sumifs with multiple criteria in one column.

[SOLVED] SumIfs with multiple criteria (search column for cells w/ text containing)

SUMIFS with 1 criteria range, multiple criteria with cell references

Bookmarks

Bookmarks

Posting Permissions