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

Lindsey P
Lindsey P ✭✭
edited 12/09/19 in Formulas and Functions


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!




Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!