Sumifs formula help

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!

Find more posts tagged with

Formulas

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…