COUNTIFS / OR - INVALID DATA TYPE
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.
Best Answer
-
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"))
Answers
-
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:
- Counts rows where the range 4 matches the primary column, range 3 is "Release 1", and range 5 is "Yes".
- 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
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
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?
-
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.bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
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
-
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"))
-
Oh let me try this, thank you. Will let you know!
-
You are a star, thank you so much!!! This worked a treat!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!