COUNTIFS / OR - INVALID DATA TYPE

Angela Logie

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"))

Thanks in advance.

Paul Newcome

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"))

Bassam Khalil

Hi @Angela Logie

please try the following formula:

=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.

Angela Logie

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?

Bassam Khalil

Hi @Angela Logie,

please try the following formula:

=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.

Angela Logie

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

Paul Newcome

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"))

Angela Logie

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

Angela Logie

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

Paul Newcome

Happy to help. 👍️