Formula issue

[normpam]

The first part of my formula evaluates correctly to 115.54 : =SUMIFS(TimeHours,ProviderID,F5,ProcedureCode,"<>Staff Training",ProcedureCode,"<>Scheduled Time Off",Labels,"<>*Full Time*")

The second part of my formula, taken separately also works just fine, and evaluates to 45 : SUMIFS(TimeHours,ProviderID,F5,Labels,"<>*Full Time*",Dates,">="&Wk1Begin,Dates,"<="&Wk1End)-40

If I simply subtract the second part from the first, I should therefore get 70.54. But I don't. I only get -13.46!!

=SUMIFS(TimeHours,ProviderID,F5,ProcedureCode,"<>Staff Training",ProcedureCode,"<>Scheduled Time Off",Labels,"<>*Full Time*")-SUMIFS(TimeHours,ProviderID,F5,Labels,"<>*Full Time*",Dates,">="&Wk1Begin,Dates,"<="&Wk1End)-40

What am I missing?
Much thanks.

 

[Fluff]

Your 2nd sumifs returns 85 before you subtract the 40, therefore your combined formula equates to =115.54 - 85 - 40 which by my reckoning should return -9.46
You either need to wrap your second formula in brackets, or add 40 rather than subtract it.

 

[johnnyL]

Did you try putting your second part of formula in parenthesis?

 

[normpam]

Your 2nd sumifs returns 85 before you subtract the 40, therefore your combined formula equates to =115.54 - 85 - 40 which by my reckoning should return -9.46
You either need to wrap your second formula in brackets, or add 40 rather than subtract it.

"If I only had a brain".... (sung to the tune of the scarecrow form Wizard of Oz)
Thanks. Haven't figured out where to put the extra parentheses, but the +40 works like a charm (my original number should have been 117.54)

 

[normpam]

Did you try putting your second part of formula in parenthesis?

"If I only had a brain".... (sung to the tune of the scarecrow form Wizard of Oz)
Thanks. Haven't figured out where to put the extra parentheses, but the +40 works like a charm (my original number should have been 117.54)

 

[Fluff]

Glad to help & thanks for the feedback. The extra brackets would be

=SUMIFS(TimeHours,ProviderID,F5,ProcedureCode,"<>Staff Training",ProcedureCode,"<>Scheduled Time Off",Labels,"<>*Full Time*")-(SUMIFS(TimeHours,ProviderID,F5,Labels,"<>*Full Time*",Dates,">="&Wk1Begin,Dates,"<="&Wk1End)-40)

 

[johnnyL]

Haven't figured out where to put the extra parentheses, but the +40 works like a charm (my original number should have been 117.54)

one at the beginning of your second formula and one at the end of your second formula.

 

[normpam]

one at the beginning of your second formula and one at the end of your second formula.

Much thanks! And if you do go to Oz, PLEASE bring me back a brain!