# Sumifs formula help

Options
edited 12/09/19

Hi, I am putting together a sumifs formula and it works for all criteria except this situation:

With my working formulas, my range is just a column value but now I need a formula with the same criteria but I need to range to be the sum of 2 columns.

This is my working formula with a single column range  =SUMIFS({2019 Master Tracker Total PO}, {2019 Master Tracker Customer}, "Crown", {2019 Master Tracker LOB}, "Maintenance")

The formula I am having issues with would be the same except the range would be {2019 Master Tracker Total PO} - {2019 Master Tracker Materials}

Thanks!

Tags:

• ✭✭✭✭✭✭
Options

Would that be Total PO MINUS Materials?

• Options

Yes, correct, sorry should have specified that.

• ✭✭✭✭✭✭
Options

The easiest way to do this would be to add a helper column on the master sheet that does that calculation. Then use that helper column as the range in your x-sheet reference.

• Options

Ah - didn't think of that one. I'll give it a try.

Thanks!

• ✭✭✭✭✭✭
Options
• Employee
Options

Hello,

Happy to help! If you'd like to SUM two ranges if they both meet the same criteria outlined by a SUMIFS formula you can achieve this by utilizing two SUMIFS Functions within a SUM Function.

Similar to this:

=SUM(SUMIFS({2019 Master Tracker Total PO}, {2019 Master Tracker Customer}, "Crown", {2019 Master Tracker LOB}, "Maintenance"),(SUMIFS({2019 Master Tracker Materials}, {2019 Master Tracker Customer}, "Crown", {2019 Master Tracker LOB}, "Maintenance"))

Cheers,

Eric

Smartsheet Support

• ✭✭✭✭✭✭
edited 02/15/19
Options

This would work as well although in this particular case it would have to be

=SUMIFS({First Range},.............) - SUMIFS({Second range},.............)

since you are wanting to subtract the one from the other.

P.S. There is also an additional open parenthesis just before the second SUMIFS in Eric's formula above that would need removed should you choose to go that particular route.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!