COUNTIFS / OR - INVALID DATA TYPE

Angela Logie
Angela Logie ✭✭✭✭
edited 03/14/24 in Formulas and Functions

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 03/13/24

    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.

    PMP Certified

    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"

  • Angela Logie
    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
    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.

    PMP Certified

    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"

  • Angela Logie
    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
    Paul Newcome ✭✭✭✭✭✭
    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"))

  • Angela Logie
    Angela Logie ✭✭✭✭

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

  • Angela Logie
    Angela Logie ✭✭✭✭

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!