Excel VBA Tables: SumIf

Question

I have 2 tables, one is "RSO" and the other is "ESP". In the column ADD of the table ESP a need to write a SUMIF function to sum the RESULT column if the RANGE column matches the CRITERIA column, but it is not working:

ActiveSheet.ListObjects(1).ListColumns("ADD").DataBodyRange.Formula = Application.WorksheetFunction.SumIf(Worksheets("RSO").ListObjects(1).ListColumns("RANGE").DataBodyRange, _
Worksheets("ESP").ListObjects(1).ListColumns("Criteria").DataBodyRange, _
Worksheets("RSO").ListObjects(1).ListColumns("RESULT").DataBodyRange)

Could anyone tell me what is the right way to do this?

Thanks!!

Answer 1

You want to set the formula of the worksheet cells, so you have to use the worksheet formula syntax, not the VBA syntax.

ActiveSheet.ListObjects("ESP").ListColumns("ADD").DataBodyRange.Formula = "=SUMIF(RSO[RANGE],[@CRITERIA],RSO[RESULT])"

As long as your tables are scoped to the workbook, you don't need to worry about specifying the worksheet names of the tables in the range formula.

It's probably safer to use the table name ListObjects("ESP") instead of the table index number ActiveSheet.ListObjects(1), in case someone runs the macro with the wrong sheet active.