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

Options

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

  • Shaine Greenwood
    Options

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

     

  • Kal-El
    Options

    Thanks, I will try it

  • Josh Garcia
    Options

    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 ✭✭✭✭✭✭
    Options

    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

     

  • Josh Garcia
    Options

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

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

    You are welcome.

    Craig

  • diegoartorres
    diegoartorres ✭✭
    edited 10/18/18
    Options

    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.