Sum If formula question - Don't want to sum if it's a loss

Lindsey P

Hello!

I'm currently using a Sum If formula that works to sum up by each lead, however, once we turn the Sales Stage to Loss I would like to remove the losses from the sum for that person's weighted forecast. The formula below is filtering to just sum up the weighted forecast for John Smith but I want to exclude anything that has the status changed to loss instead of including it in the formula. Is it possible to set up sum if to have an "AND not [5 - Closed Lost]) or something like that?

I want to take this one and add in something to not include the loss status items:

=SUMIF(CHILDREN([Lead]53),"John Smith", Children([Weighted Forecast]53)) 

This is what I was trying but getting the #unparseable when I try variations of this:

=SUMIF(CHILDREN([Lead]53),"John Smith", AND If (NOT([Sales Stage = "5 - Closed Lost"])), Children([Weighted Forecast]53))    

Other work arounds would be that we could delete any rows that become a loss and move it to another Smartsheet, or change the weighted forecast cell to zero. I want to sum up the losses separately so I don't really want to change to zero (to show how much was lost) and I would prefer if it could stay on the same sheet instead of moving the losses to another sheet.

Thanks for any tips and insight!

 

J. Craig Williams

First. never use SUMIF. When you want to expand it will be easier if you started from SUMIFS.

Here's the formula for that:

=SUMIFS(CHILDREN([Weighted Forecast]@row), CHILDREN(Lead@row), "John Smith", CHILDREN([Sales Stage]@row), @cell <> "5 - Closed Lost")

Change @row to your row number (53)

The only time you should consider using AND inside one of the IFS family of functions is if you also need an OR. The syntax is already getting the AND functionality for you.

Cheers,

Craig

Lindsey P

Thank you so much!

At first I must have missed something but it is working now. Happy dance!

=SUMIFS(CHILDREN([Weighted Forecast]53), CHILDREN([Team Lead]53), "John Smith", CHILDREN([Sales Stage]53), @cell <> "5 - Closed Lost")

J. Craig Williams