creating tables with sumifs( multiple criteria

[BryceVBA]

I'm trying to set up some tables for a forcast vs Actuals report, but I'm having some trouble with the SUMIFS( formula.

**See attachment to know what I'm talking about.

So going through the SUMIFS formula for forecast (PV0),

the sum range is of course the forecasted column correlating to the same month as the formula,

Criteria1 is making sure that the right program is selected correlating to the table title,

Criteria2 is summing specifically the labor data points based on their titles(PS Value Category). For example, I only want Direct labor, Labor overhead, Offbook Adjustments, Offbook Overhead, and Unallowable Cost to be summed in this cell.

Please Login or Register  to view this content.

The first formula doesn't like the Or( formula inside it, or it might be misplaced?
The second formula works fine, but can only work for one of the five PS Value Category
The third formula is an extention of the second, but doesn't work because the sumifs( formula is looking for both PS Value Category criteria in the same cell, which isn't going to happen. I think this formula requires an Or( formula somewhere, but I'm not sure where.

[Skiptomylou]

Hi there,

Instead of using OR, you can place your additional options in brackets {} like the following.

Please Login or Register  to view this content.

Edit: My fault for leaving the outer SUM off originally.

[JohnTopley]

Try


=SUM(SUMIFS('Raw data'!Y:Y,'Raw data'!$A:$A,"BA",'Raw data'!$L:$L,{"DIRECT LABOR","LABOR OVERHEAD","OFFBOOK ADJUST","OFFBOOK OH","UNALLOWABLE"}))

[BryceVBA]

Hi there,

Instead of using OR, you can place your additional options in brackets {} like the following.

Please Login or Register  to view this content.

Hello, and thanks for your input.

This formula still only sums the DIRECT LABOR variable and ignores the others. I tried a similar formula to ensure that the first criteria isn't the problem.

Please Login or Register  to view this content.

As I said before, this formula only sums the LABOR OVERHEAD input. Is different syntax needed to separate the variables?

[BryceVBA]

Try


=SUM(SUMIFS('Raw data'!Y:Y,'Raw data'!$A:$A,"BA",'Raw data'!$L:$L,{"DIRECT LABOR","LABOR OVERHEAD","OFFBOOK ADJUST","OFFBOOK OH","UNALLOWABLE"}))

Thanks for the reply!

This formula looks correct, but why is the additional Sum( formula needed? Who would have thought to sum the sum formula Sumception

[leelnich]

John beat me to it. You have to wrap the SUMIFS inside an extra SUM function because it's returning an array of results - one for each bracketed value - like this: {SUMIF(..."DIRECT LABOR"),SUMIF(..."LABOR OVERHEAD"),SUMIF(..."OFFBOOK ADJUST"), etc.}