VB Macro SUM IF

[JackRyan82]

All, I have a sumifs that I want to convert to VB and i have been struggling for a few days. The SUMIFS is causing issues for my excel document as it integrates with an ERP package. I have identified that the formula, not in VB, is the issue even though it calculates the correct amounts (different issue).

=SUMIFS(Data!X9:X100,Data!O9:O100, 'Capital - Supl'!K1,Data!I9:I100,"5*")

I have no problem getting my SUMIFS to work within the cell however I am struggling to get it crafted in VB. Can someone help me. I am new so forgive any missing information.

Essentially I need to sum the values in column X9:X100 when the values in column O9:O100 matches the value in worksheet Capital - Supl cell K1 where the values in column I9:I100 starts with a 5.

 

[AlphaFrog]

Try something like this...

MyTotal = Evaluate("=SUMIFS(Data!X9:X100,Data!O9:O100, 'Capital - Supl'!K1,Data!I9:I100,""5*"")")

 

[Peter_SSs]

Welcome to the MrExcel board!

Not sure what sheet or what cell(s) you want the formula in, but the gist of it is that you have to double-up on the quote marks when inserting the formula by vba.
So it would be something like this

Range("A2").Formula = "=SUMIFS(Data!X9:X100,Data!O9:O100, 'Capital - Supl'!K1,Data!I9:I100,""5*"")"

 

[JackRyan82]

Try something like this...

MyTotal = Evaluate("=SUMIFS(Data!X9:X100,Data!O9:O100, 'Capital - Supl'!K1,Data!I9:I100,""5*"")")

Thank you AlphaFrog. This did the trick.

 

[JackRyan82]

Thank you Mr. Excel! I appreciate the warm welcome. This did the trick as well. I appreciate the community here.

 

[Peter_SSs]

Thank you Mr. Excel! I appreciate the warm welcome. This did the trick as well. I appreciate the community here.

Glad you got a successful outcome.