Help with contains function.

TruCal

We have multiple end users of our product I would like to count how many go to these end users and satisfy a list of other criteria. I would like to wrap the contains formula within count if formula.

My "handed over to" can be x, y, z, or any combination thereof

I would like to count how many entries have been handed over to x for example

So far I have tried

=COUNTIFS({activity}, "working", {Status}, status@row, {children}, =0, {Deliverable}, type@row, {real finish}, >=[year start]1, {real finish}, <=[date Q4]1, {hand over to}, "x"

=COUNTIFS({activity}, "working", {Status}, status@row, {children}, =0, {Deliverable}, type@row, {real finish}, >=[year start]1, {real finish}, <=[date Q4]1, (IF(CONTAINS("x", {hand over to}1,0))

both return #UNPAREABLE.

Many thanks in advance for any help.

Take care

J Tech

Hi @TruCal,

Here's an updated formula that should count the number of entries that have been handed over to "x" and meet the other criteria you listed:

=COUNTIFS({activity}, "working", {Status}, status@row, {children}, 0, {Deliverable}, type@row, {real finish}, ">="&[year start]1, {real finish}, "<="&[date Q4]1, {hand over to}, CONTAINS("x"))

In this formula, I've made the following changes:

• Removed the equal sign from the children criterion, since the value should be 0 instead of =0
• Used "&" to concatenate the comparison operators with the year start and date Q4 values, which should be in date format
• Wrapped the "x" in quotes and used CONTAINS to check if the "hand over to" column contains the string "x"

Andrée Starå

Hi @J Tech

I hope you're well and safe!

This formula won't work either because it's for Excel and not Smartsheet. For example, you can't use the & in a Smartsheet formula like yours.

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

TruCal

Thanks @J Tech and @Andrée Starå for your responses and your time. @Andrée Starå what is your suggested correction for full formula? Sorry I was unclear how-to edit. Many thanks once again.

J Tech

Sorry try

=COUNTIFS({activity}, "working", {Status}, status@row, {children}, 0, {Deliverable}, type@row, {real finish}, >="[year start]@row, {real finish}, <=[date Q4], {hand over to}@row, CONTAINS("x"))

TruCal

Thanks so much @J Tech . Still getting same error back.

The column it is looking up from is in another sheet formatted as a drop down list with multiple values per cell. Is this what is causing problems? Can't identify "x" in xy etc entries?

J Tech

Yes it does affect the formula:

try the below

=COUNTIFS({activity}, "working", {Status}, status@row, {children}, 0, {Deliverable}, type@row, {real finish}, >="[year start]@row, {real finish}, <=[date Q4], {hand over to}@row, HAS(@cell,"x"))

TruCal

Hi

thanks for your continued help. This didn't work for me but I have found a solution. What I ended up doing was adding a column per option in drop down x,y,z etc.

Countifs([hand over to]@row:[hand over to] @row, has,(@cell, "x"))

Then I did "count if " formula in second sheet for 1 with other filters rather than nesting as a single formula.

=COUNTIFS({real finish}, >=[year start]1, {real finish}, <=[date Q4]1, {activity}, "working", {Status}, status@row, {children}, 0, {Deliverable}, type@row)

I'm sure there is a more elegant solution but this works for me for now.

Really appreciate the help , I wouldn't have got to this point wiout your suggestions even if the solution is different in the end.

Take care

TruCal

Not sure why there are so many returns in post. Sorry for all reading on phone screens!