Formula to Count If NOT one of

06/30/21
Accepted

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([email protected] < 2, SUM(CHILDREN()), IF([email protected] = 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 VangToufong Vang ✭✭✭
    Accepted 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([email protected] < 2
    	, SUM(CHILDREN())
    	, IF([email protected] = 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([email protected] < 2, SUM(CHILDREN()), IF([email protected] = 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([email protected] < 2
    	, SUM(CHILDREN())
    	, IF([email protected] = 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([email protected] < 2, SUM(CHILDREN()), IF([email protected] = 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 VangToufong Vang ✭✭✭
    Accepted 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([email protected] < 2
    	, SUM(CHILDREN())
    	, IF([email protected] = 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([email protected] < 2, SUM(CHILDREN()), IF([email protected] = 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([email protected] < 2
    	, SUM(CHILDREN())
    	, IF([email protected] = 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([email protected] < 2, SUM(CHILDREN()), IF([email protected] = 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!

  • Leslie EdwardsLeslie Edwards ✭✭✭✭

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

Sign In or Register to comment.