# COUNTIFS / OR - INVALID DATA TYPE

Options
✭✭✭✭✭
edited 03/14/24

Looking for your help, I'm trying to get the below formula to work. Let me know if you need additional information:

=COUNTIFS({Albaugh, LLC - RICEFW Tracker Range 4}, [Primary Column]@row, {Albaugh, LLC - RICEFW Tracker Range 3}, "Release 1", OR({Albaugh, LLC - RICEFW Tracker Range 5}, "Yes", {Albaugh, LLC - RICEFW Tracker Range 5}, "Added"))

• ✭✭✭✭✭✭
Options

Here is how you use an OR function in a COUNTIFS to avoid having to keep adding and subtracting separate COUNTIFS:

=COUNTIFS({Albaugh, LLC - RICEFW Tracker Range 4}, [Primary Column]@row, {Albaugh, LLC - RICEFW Tracker Range 3}, "Release 1", {Albaugh, LLC - RICEFW Tracker Range 5}, OR(@cell = "Yes", @cell = "Added"))

If you want to count every row that is NOT this or that, you would actually use an AND.

=COUNTIFS({Albaugh, LLC - RICEFW Tracker Range 4}, [Primary Column]@row, {Albaugh, LLC - RICEFW Tracker Range 3}, "Release 1", {Albaugh, LLC - RICEFW Tracker Range 5}, OR(@cell = "Yes", @cell = "Added"), {Life Cycle Status}, AND(@cell <> "Deferred", @cell <> "Rejected"))

• ✭✭✭✭✭✭
edited 03/13/24
Options

Hi @Angela Logie

```=COUNTIFS(
{Albaugh, LLC - RICEFW Tracker Range 4}, [Primary Column]@row,
{Albaugh, LLC - RICEFW Tracker Range 3}, "Release 1",
{Albaugh, LLC - RICEFW Tracker Range 5}, "Yes"
)
+
COUNTIFS(
{Albaugh, LLC - RICEFW Tracker Range 4}, [Primary Column]@row,
{Albaugh, LLC - RICEFW Tracker Range 3}, "Release 1",
{Albaugh, LLC - RICEFW Tracker Range 5}, "Added"
)

```

This formula does two separate counts:

1. Counts rows where the range 4 matches the primary column, range 3 is "Release 1", and range 5 is "Yes".
2. Counts rows where the range 4 matches the primary column, range 3 is "Release 1", and range 5 is "Added".

Then it adds the results of these two counts together to get the final count.

Please adjust the named ranges to match the exact names of your cross-sheet references in Smartsheet. This should give you the count of rows that meet your criteria.

bassam.khalil2009@gmail.com

• ✭✭✭✭✭
Options

Bassam,

Firstly, the below worked thank you!

=COUNTIFS({Albaugh, LLC - RICEFW Tracker Range 4}, [Primary Column]@row, {Albaugh, LLC - RICEFW Tracker Range 3}, "Release 1", {Albaugh, LLC - RICEFW Tracker Range 5}, "Yes") + COUNTIFS({Albaugh, LLC - RICEFW Tracker Range 4}, [Primary Column]@row, {Albaugh, LLC - RICEFW Tracker Range 3}, "Release 1", {Albaugh, LLC - RICEFW Tracker Range 5}, "Added")

Secondly, what if I also want to add in another OR statement i.e. column 'Life Cycle Status' is not "Deferred" or "Rejected". How do I add this into the above formula?

• ✭✭✭✭✭✭
Options

Hi @Angela Logie,

```=COUNTIFS({Albaugh, LLC - RICEFW Tracker Range 4}, [Primary Column]@row, {Albaugh, LLC - RICEFW Tracker Range 3}, "Release 1", {Albaugh, LLC - RICEFW Tracker Range 5}, "Yes")
+ COUNTIFS({Albaugh, LLC - RICEFW Tracker Range 4}, [Primary Column]@row, {Albaugh, LLC - RICEFW Tracker Range 3}, "Release 1", {Albaugh, LLC - RICEFW Tracker Range 5}, "Added")
- COUNTIFS({Albaugh, LLC - RICEFW Tracker Range 4}, [Primary Column]@row, {Albaugh, LLC - RICEFW Tracker Range 3}, "Release 1", {Albaugh, LLC - RICEFW Tracker Range 6}, "Deferred")
- COUNTIFS({Albaugh, LLC - RICEFW Tracker Range 4}, [Primary Column]@row, {Albaugh, LLC - RICEFW Tracker Range 3}, "Release 1", {Albaugh, LLC - RICEFW Tracker Range 6}, "Rejected")
```

This formula adds the counts of "Yes" and "Added" like before but then subtracts the counts where 'Life Cycle Status' is "Deferred" and separately where it is "Rejected". This effectively removes those you don't want included in your total count.

Please adjust `{Albaugh, LLC - RICEFW Tracker Range 6}` to match the actual reference for your 'Life Cycle Status' column. This solution assumes that the combination of conditions (Release 1 with either Yes or Added, and not Deferred or Rejected) is mutually exclusive, meaning a row wouldn't be counted in both the addition and subtraction parts of the formula.

bassam.khalil2009@gmail.com

• ✭✭✭✭✭
Options

Bassam,

Thank you , so the formula runs with no issues, the number I should get 23 however its only pulling in 19 so something cant be right? Any ideas?

And thank you so much for your help!

Ang

• ✭✭✭✭✭✭
Options

Here is how you use an OR function in a COUNTIFS to avoid having to keep adding and subtracting separate COUNTIFS:

=COUNTIFS({Albaugh, LLC - RICEFW Tracker Range 4}, [Primary Column]@row, {Albaugh, LLC - RICEFW Tracker Range 3}, "Release 1", {Albaugh, LLC - RICEFW Tracker Range 5}, OR(@cell = "Yes", @cell = "Added"))

If you want to count every row that is NOT this or that, you would actually use an AND.

=COUNTIFS({Albaugh, LLC - RICEFW Tracker Range 4}, [Primary Column]@row, {Albaugh, LLC - RICEFW Tracker Range 3}, "Release 1", {Albaugh, LLC - RICEFW Tracker Range 5}, OR(@cell = "Yes", @cell = "Added"), {Life Cycle Status}, AND(@cell <> "Deferred", @cell <> "Rejected"))

• ✭✭✭✭✭
Options

Oh let me try this, thank you. Will let you know!

• ✭✭✭✭✭
Options

You are a star, thank you so much!!! This worked a treat!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!