Exclusion Formula for Sum/Count

Annie_B

I am trying to get a total of my "Forecasted Amount" column which should include the following stages "0-Lead"+"2 - Qualified"+"3 - Proposal"+ "4 - Final (EL)"+"5 - Win".  

Currenly I have it set up to only not include the "6 - Lost" with this formula:

=SUMIFS(CHILDREN([Forecasted Amount]52), CHILDREN([Referral 1/Critical]52), Stage8, CHILDREN(Stage52), @cell <> "6 - Lost") 

 

Is there a way to either get it to also not include (<>) "1 - Unqualified", "7 - Inactive" and "8 - Hold" as well?

 

OR a formula for it to add the above amounts? 

 

Paul Newcome

Sure. All you need is to build in an OR statement for the criteria.

 

=SUMIFS(CHILDREN([Forecasted Amount]52), CHILDREN([Referral 1/Critical]52), Stage8, CHILDREN(Stage52), OR(@cell <> "6 - Lost", @cell <> "1 - Unqualified", @cell <> "7 - Inactive", @cell <> "8 - Hold")) 

Annie_B

This worked perfectly!! Thank you so much!

Paul Newcome

Excellent! Happy to help.