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

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 backend (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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!
Answers

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

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 backend (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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

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!
😁

@Cbastien Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

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:
Thank you for your help M.Newcome!😅
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.9K Get Help
 379 Global Discussions
 211 Industry Talk
 441 Announcements
 4.6K Ideas & Feature Requests
 140 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 301 Events
 33 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!