COUNTIF USING OR

Kal-El

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!

Find more posts tagged with

Formulas

Comments

Shaine Greenwood

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

Thanks, I will try it

Josh Garcia

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

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

Josh Garcia

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

J. Craig Williams

You are welcome.

Craig

diegoartorres

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