or Explore Discussions

# 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!

• ✭✭✭

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!

• ✭✭✭

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!

• ✭✭✭✭

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