Fonctions: FIND vs CONTAINS

Cbastien

Hi community!

Excuse me if it sounds like a dumb question but I just can't find any hint that would help me to choose between these functions to get the fastest in execution and most efficient.

Here's two formulas that gives the same results:

=SUMIFS([ITEM qty]:[ITEM qty]; [COMPLETED]:[COMPLETED]; <>0; [DEFINITION]:[DEFINITION]; CONTAINS("TYPE"; @cell))

=SUMIFS([ITEM qty]:[ITEM qty]; [COMPLETED]:[COMPLETED]; <>0; [DEFINITION]:[DEFINITION]; FIND("TYPE"; @cell) > 0)

these returns the qty of a specifc item that has been marked as completed and based on a string found in the description column.

Also, feel free to comment if you think you could simplify the formula since I am not a programer.

The execution delay is important to us because installers are working on field with tablets and our sheets are getting heavier with time.

Any help would be appreciated 😁

Thank you

Paul Newcome

The three biggest differences between CONTAINS and FIND are...

1) FIND is case sensitive, but CONTAINS is not. This distinction could be useful if you have a free text cell that you are evaluating and users may not always use the same capitalizations (although the LOWER and UPPER functions can help with that).

2) CONTAINS is going to be less work on the back-end (more efficient) because it simply outputs a true/false value. FIND outputs a numerical value when then requires the second process of comparing that to "greater than zero".

3) CONTAINS does not work when evaluating a contact type column. That requires either the FIND or the HAS function.

Kelly Moore

Hey @Cbastien

I wondered if you need to use either CONTAINS or Find. Is the value in [Type] in a single select dropdown, that is , a drop down where only one answer is allowed at a time?

If so, CONTAINS or FIND isn't required.

=SUMIFS([ITEM qty]:[ITEM qty]; [COMPLETED]:[COMPLETED]; <>0; [DEFINITION]:[DEFINITION]; TYPE@row)

If this works for you, this it is more efficient than searching with CONTAINS or FIND.

Kelly

Paul Newcome

The three biggest differences between CONTAINS and FIND are...

1) FIND is case sensitive, but CONTAINS is not. This distinction could be useful if you have a free text cell that you are evaluating and users may not always use the same capitalizations (although the LOWER and UPPER functions can help with that).

2) CONTAINS is going to be less work on the back-end (more efficient) because it simply outputs a true/false value. FIND outputs a numerical value when then requires the second process of comparing that to "greater than zero".

3) CONTAINS does not work when evaluating a contact type column. That requires either the FIND or the HAS function.

Cbastien

Good afternoon to you both Kelly and Paul.

First, thank you for doing your best to help the beginers like me!😅

@Kelly Moore : Since I have to find some words through a sentence I think I must use one of these.

@Paul Newcome : Thank you for helping me to choose the right one for my purpose (it's gonna be CONTAINS). I did'nt realise the important info you just bring me here..

Thanks again to you both for your fast feedback!

😁

Paul Newcome

@Cbastien Happy to help. 👍️

Cbastien

Hi @Paul Newcome !

I've posted another problem recently and I would be very grateful if you could take a look at it since you are a Smartsheet hero to me! 🤩

Here's the link to the discussion in case you have spare time to look at it:

https://community.smartsheet.com/discussion/108183/formula-to-fill-the-blank-rows-with-the-value-above-using-a-relative-row-id

Thank you for your help M.Newcome!😅