Fonctions: FIND vs CONTAINS

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

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!