Formula to Count If NOT one of

Leslie Edwards

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!

Toufong Vang

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!

Toufong Vang

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!

Leslie Edwards

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