sumifs in VBA

Hello all, new here, but this site looks like a great resource for what I do.

I'm trying to assign the results of a conditional sum with two conditions to a variable of type double. MS documentation on sumifs in VBA is vague, and I haven't been able to find anything useful here or on google. Seems no matter what I do, the function returns nothing.

Code:

---

   
    Dim maj_group, bill_summary, mtd_ttl_hours As Range
    maj_group = "C:C"
    bill_summary = "D:D"
    mtd_ttl_hours = "E:E"

    MsgBox Application.WorksheetFunction.SumIfs(mtd_ttl_hours, maj_group, bill_summary, "ENG", "LABOR")

---

The code above will not even generate a msgbox, and assigning the function result to a double variable always gives 0. The worksheet this procedure runs against has a variable number of rows, but I have already tried to define all the ranges using fixed rows (ie - C1:C100) but to no avail.

Is the syntax for sumifs in VBA

Application.WorksheetFunction.SumIfs(sum_range, criteria_range1, criteria_range2, criteria1, criteria2)

-or-

Application.WorksheetFunction.SumIfs(sum_range, criteria_range1, criteria1, criteria_range2, criteria2)

(and so on and so forth for more criteria)

Neither seem to have an effect on the result. I've been picking at this for days, and it's really holding me up. It works fine if I write a sumifs formula in a cell in the sheet, but I get nothing when running it in a macro. Anyone have any experience with this? I can provide the entire xlsm file if anyone thinks it will be of help.

I don't have xl2007 however maj_group should be a range but it looks like it's a string, likewise bill_summary.
To define them as ranges dim each one as range:
[vba]Dim maj_group As range, bill_summary As Range, mtd_ttl_hours As Range[/vba].
Then use the Set statement:
[vba]set maj_group = range("C:C")
set bill_summary = range("D:D")
set mtd_ttl_hours = range("E:E")[/vba]you may want to qualify the ranges if they're not on the active sheet.

Could you clarify what you mean by qualifying the ranges (my formal programming training is ridiculously out of date)?

Right now I have this and it still is giving me a value of zero. I changed a couple other things as well to help me debug:

[VBA]
Dim eng_hours As Double

Dim maj_group As Range, bill_summary As Range, mtd_ttl_hours As Range
Set maj_group = Range("C:C")
Set bill_summary = Range("D:D")
Set mtd_ttl_hours = Range("E:E")

eng_hours = Application.WorksheetFunction.SumIfs(mtd_ttl_hours, maj_group, bill_summary, "ENG", "LABOR")
Debug.Print eng_hours[/VBA]

well
Range("B:B")
is column B on the active sheet, but if the active sheet doesn't happen to be the right sheet when the code runs you might get the wrong results, so you might qualify the range with:
Sheets("Sheet1").Range("B:B")
which will always be column B on on Sheet1, regardless if another sheet happens to be the active sheet. This can be taken further to protect against the wrong sheet1 being referred to; you might have more than one workbook open with a Sheet1 in it, and if the wrong workbook is active...
So you can use the likes of
Thisworkbook.Sheets("Sheet1").Range("B:B")
or
Workbooks("myfile.xls").Sheets("Sheet1").Range("B:B")
etc. etc.

When testing/developing code there's no need as you'll make sure the right sheet is active when you step through the code.

Regarding it returning 0, first try not contsraining the variable to double - it should work but, test it without doing this and see if it still gives 0. Second, I seem to remember that SumIf in versions up to XL2003 won't work with a full column, try the likes of Range("B1:B1000") in the formula instead (I think I might be wrong here (edited after posting)). Finally, try the formula on the worksheet itself and see if it works there, then translate the formula to vba.

Don't have a sample work book (hint, hint) but it looks like the arguments might be out of order, in addition to the other comments

Quote:

---

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

---

[vba]
eng_hours = Application.WorksheetFunction.SumIfs(mtd_ttl_hours, maj_group, "ENG", bill_summary, "LABOR")
[/vba]

Paul

Looks like I got it figured out!

First off, the syntax of sumifs is the same in VBA as in the worksheet formula. That was my biggest problem, although using the set statement is necessary if you're going to define the ranges in the formula using variables. Entire columns worked ok as well using XL2007

Here is the result:
[VBA] Dim eng_hours As Double

Dim maj_group As Range, bill_summary As Range, mtd_ttl_hours As Range
Set maj_group = Range("C:C")
Set bill_summary = Range("D:D")
Set mtd_ttl_hours = Range("E:E")

eng_hours = Application.WorksheetFunction.SumIfs(mtd_ttl_hours, maj_group, "ENG", bill_summary, "LABOR")[/VBA]

thanks a ton for you help .45!

edit - you're right Paul. I don't really know why I ever thought it would be different from the syntax used in the worksheet