Formula to Count If NOT one of

Hello,

I have a formula that is counting based on specific role, but now I want to adjust this formula in a new sheet to only count if not in those specific roles. How can I use the OR function with a not or doesn't equal? Here is the current formula - the bold section is where I want to adjust to not count these roles (there are too many other roles to use the same formula and list them all out). I tried to use <> instead of = but that didn't adjust the current count with this formula.

=IF(Level@row < 2, SUM(CHILDREN()), IF(Level@row = 2, COUNTIFS({Onboarding Tracker - Offer Signed}, >=[Start Date]@row, {Onboarding Tracker - Offer Signed}, <=[End Date]@row, {Onboarding Tracker - Recruiter}, [Recruiter Responsible]@row, {Onboarding Tracker - Role}, OR(@cell = "Associate BT", @cell = "BT", @cell = "LBT", @cell = "SBT", @cell = "IA")) + COUNTIFS({Inactive New Hires - Offer Signed}, >=[Start Date]@row, {Inactive New Hires - Offer Signed}, <=[End Date]@row, {Inactive New Hires - Recruiter}, [Recruiter Responsible]@row, {Inactive New Hires - Role}, OR(@cell = "Associate BT", @cell = "BT", @cell = "LBT", @cell = "SBT", @cell = "IA"))))

THANK YOU!

Best Answer

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    Answer ✓

    Hi, Leslie.

    Try...

    Evaluate the cell and exclude it from the count if the role is one of five that you don't want.

    = IF(Level@row < 2
    	, SUM(CHILDREN())
    	, IF(Level@row = 2
    		,IF( OR(@cell = "Associate BT", @cell = "BT", @cell = "LBT", @cell = "SBT", @cell = "IA")
    		   , ""
    		   , COUNTIFS(
    				{Onboarding Tracker - Offer Signed}, >=[Start Date]@row
    				,{Onboarding Tracker - Offer Signed}, <=[End Date]@row
    				,{Onboarding Tracker - Recruiter}, [Recruiter Responsible]@row)
                        +  COUNTIFS(
    				 {Inactive New Hires - Offer Signed}, >=[Start Date]@row
    				,{Inactive New Hires - Offer Signed}, <=[End Date]@row
    				,{Inactive New Hires - Recruiter},[Recruiter Responsible]@row)
    		  )
    	   )	
      )
    

    = IF(Level@row < 2, SUM(CHILDREN()), IF(Level@row = 2,IF( OR(@cell = "Associate BT", @cell = "BT", @cell = "LBT", @cell = "SBT", @cell = "IA"), "", COUNTIFS({Onboarding Tracker - Offer Signed}, >=[Start Date]@row,{Onboarding Tracker - Offer Signed}, <=[End Date]@row,{Onboarding Tracker - Recruiter}, [Recruiter Responsible]@row) + COUNTIFS({Inactive New Hires - Offer Signed}, >=[Start Date]@row,{Inactive New Hires - Offer Signed}, <=[End Date]@row,{Inactive New Hires - Recruiter},[Recruiter Responsible]@row))))

    You can also try using NOT():

    NOT( OR(@cell = "Associate BT", @cell = "BT", @cell = "LBT", @cell = "SBT", @cell = "IA") )

    =IF(Level@row < 2
    	, SUM(CHILDREN())
    	, IF(Level@row = 2
    		, COUNTIFS({Onboarding Tracker - Offer Signed}, >=[Start Date]@row
    			, {Onboarding Tracker - Offer Signed}, <=[End Date]@row
    			, {Onboarding Tracker - Recruiter}, [Recruiter Responsible]@row
    			, {Onboarding Tracker - Role}, NOT(OR(@cell = "Associate BT", @cell = "BT", @cell = "LBT", @cell = "SBT", @cell = "IA")))
    		+ COUNTIFS({Inactive New Hires - Offer Signed}, >=[Start Date]@row
    			, {Inactive New Hires - Offer Signed}, <=[End Date]@row
    			, {Inactive New Hires - Recruiter}, [Recruiter Responsible]@row
    			, {Inactive New Hires - Role}, NOT(OR(@cell = "Associate BT", @cell = "BT", @cell = "LBT", @cell = "SBT", @cell = "IA")))
    	)
    )
    

    =IF(Level@row < 2, SUM(CHILDREN()), IF(Level@row = 2, COUNTIFS({Onboarding Tracker - Offer Signed}, >=[Start Date]@row, {Onboarding Tracker - Offer Signed}, <=[End Date]@row, {Onboarding Tracker - Recruiter}, [Recruiter Responsible]@row, {Onboarding Tracker - Role}, NOT(OR(@cell = "Associate BT", @cell = "BT", @cell = "LBT", @cell = "SBT", @cell = "IA"))) + COUNTIFS({Inactive New Hires - Offer Signed}, >=[Start Date]@row, {Inactive New Hires - Offer Signed}, <=[End Date]@row, {Inactive New Hires - Recruiter}, [Recruiter Responsible]@row, {Inactive New Hires - Role}, NOT(OR(@cell = "Associate BT", @cell = "BT", @cell = "LBT", @cell = "SBT", @cell = "IA")))))

    Hope this helps!

Answers

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    Answer ✓

    Hi, Leslie.

    Try...

    Evaluate the cell and exclude it from the count if the role is one of five that you don't want.

    = IF(Level@row < 2
    	, SUM(CHILDREN())
    	, IF(Level@row = 2
    		,IF( OR(@cell = "Associate BT", @cell = "BT", @cell = "LBT", @cell = "SBT", @cell = "IA")
    		   , ""
    		   , COUNTIFS(
    				{Onboarding Tracker - Offer Signed}, >=[Start Date]@row
    				,{Onboarding Tracker - Offer Signed}, <=[End Date]@row
    				,{Onboarding Tracker - Recruiter}, [Recruiter Responsible]@row)
                        +  COUNTIFS(
    				 {Inactive New Hires - Offer Signed}, >=[Start Date]@row
    				,{Inactive New Hires - Offer Signed}, <=[End Date]@row
    				,{Inactive New Hires - Recruiter},[Recruiter Responsible]@row)
    		  )
    	   )	
      )
    

    = IF(Level@row < 2, SUM(CHILDREN()), IF(Level@row = 2,IF( OR(@cell = "Associate BT", @cell = "BT", @cell = "LBT", @cell = "SBT", @cell = "IA"), "", COUNTIFS({Onboarding Tracker - Offer Signed}, >=[Start Date]@row,{Onboarding Tracker - Offer Signed}, <=[End Date]@row,{Onboarding Tracker - Recruiter}, [Recruiter Responsible]@row) + COUNTIFS({Inactive New Hires - Offer Signed}, >=[Start Date]@row,{Inactive New Hires - Offer Signed}, <=[End Date]@row,{Inactive New Hires - Recruiter},[Recruiter Responsible]@row))))

    You can also try using NOT():

    NOT( OR(@cell = "Associate BT", @cell = "BT", @cell = "LBT", @cell = "SBT", @cell = "IA") )

    =IF(Level@row < 2
    	, SUM(CHILDREN())
    	, IF(Level@row = 2
    		, COUNTIFS({Onboarding Tracker - Offer Signed}, >=[Start Date]@row
    			, {Onboarding Tracker - Offer Signed}, <=[End Date]@row
    			, {Onboarding Tracker - Recruiter}, [Recruiter Responsible]@row
    			, {Onboarding Tracker - Role}, NOT(OR(@cell = "Associate BT", @cell = "BT", @cell = "LBT", @cell = "SBT", @cell = "IA")))
    		+ COUNTIFS({Inactive New Hires - Offer Signed}, >=[Start Date]@row
    			, {Inactive New Hires - Offer Signed}, <=[End Date]@row
    			, {Inactive New Hires - Recruiter}, [Recruiter Responsible]@row
    			, {Inactive New Hires - Role}, NOT(OR(@cell = "Associate BT", @cell = "BT", @cell = "LBT", @cell = "SBT", @cell = "IA")))
    	)
    )
    

    =IF(Level@row < 2, SUM(CHILDREN()), IF(Level@row = 2, COUNTIFS({Onboarding Tracker - Offer Signed}, >=[Start Date]@row, {Onboarding Tracker - Offer Signed}, <=[End Date]@row, {Onboarding Tracker - Recruiter}, [Recruiter Responsible]@row, {Onboarding Tracker - Role}, NOT(OR(@cell = "Associate BT", @cell = "BT", @cell = "LBT", @cell = "SBT", @cell = "IA"))) + COUNTIFS({Inactive New Hires - Offer Signed}, >=[Start Date]@row, {Inactive New Hires - Offer Signed}, <=[End Date]@row, {Inactive New Hires - Recruiter}, [Recruiter Responsible]@row, {Inactive New Hires - Role}, NOT(OR(@cell = "Associate BT", @cell = "BT", @cell = "LBT", @cell = "SBT", @cell = "IA")))))

    Hope this helps!

  • Yes, this was exactly what I needed - thank you so much for the quick response!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!