Sumifs formula help

shornbuckle

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!

 

Paul Newcome

Would that be Total PO MINUS Materials?

shornbuckle

Yes, correct, sorry should have specified that.

Paul Newcome

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.

shornbuckle

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

Thanks!

Paul Newcome

Happy to help. 

eric.o

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

Paul Newcome

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.