Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

COUNTIF USING OR

How do I add an OR to the expression and logic:

="Resource: " + COUNTIF(CHILDREN(Status1:Status17), "Terminal Updated") + " of " + COUNT(CHILDREN(Status1:Status17))

I want to add both Terminal Updated and Terminal Replacement to the count.

 

Any help greatly appreciated!

Tags:

Comments

  • Hi Tee,

    If you're looking to have your COUNTIF count anything that meets one of multiple criteria, then you can use a formula like the following example:

    =COUNTIF(CHILDREN([Status1:Status17), OR(@cell = "Terminal Updated", @cell = "Terminal Replacement"))

    You'll want to replace your current COUNTIF with this. 

    One other note on the CHILDREN() function is that if your formula is in the parent and it's referencing its direct children (cells right underneath it) you don't need to add cell references, e.g. =COUNTIF(CHILDREN(), OR(@cell = "Terminal Updated", @cell = "Terminal Replacement"))

    If you want the formula to reference another parent's children, you can just reference the parent cell, e.g. = COUNTIF(CHILDREN(Status1), OR(@cell = "Terminal Updated", @cell = "Terminal Replacement"))

     

  • Thanks, I will try it

  • This does not actually work for me.

     

    I've published an example here if I can get some support getting it working. Screenshots attached. Error is #Invalid Operation.

    https://app.smartsheet.com/b/publish?EQBCT=491449d252c04838be1208fe5cd9c5f8

    Josh

    Screenshot_2018-04-18_17-16-25.png

    Screenshot_2018-04-18_17-16-38.png

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Responding to Josh:

    Your syntax is wrong. Your use of the [Type] column implies you are looking for something there  (car, wagon, etc...) but you aren't.

    I fixed the formula on your sheet:

    =COUNTIF(Color1:Color11, OR(@cell = "Red", @cell = "Yellow"))

    If you are wanting to use TYPE, use COUNTIFS

    I hope this helps

    Craig

     

  • And that's why J. Craig is the man.

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    You are welcome.

    Craig

  • diegoartorres
    diegoartorres ✭✭
    edited 10/18/18

    Hello everyone,

    I am looking to have an information sheet that is looking for several criteria with "and" and "or".

    For example, I want the formula to count if the other sheet has "Priority 1" and "John Doe" and a status of either 1 or 2 or 3.

    I will have another cell on the information sheet for "Priority 2" and "John Doe" and a status of either 1 or 2 or 3.

    I am not using children and the sheet I am referencing is one that I am shared with as Editor - cannot share, in case that is an issue.

    Thank you.

    Sincerely,

    Diego

This discussion has been closed.