Using SumIFs and Find referencing other sheets
I am trying to sum percentages for any tasks that are greater than 0% and contain certain text - in this example, "MFA" , among a string of text in a column. For example, the entire task name could be MFA deployment or MFA training but I want to search for any tasks that have "MFA". I have this formula started but keep getting unparseable no matter what I try. Any ideas?
=SUMIFS({Auth0 Project Plan Range 6}, {Auth0 Project Plan Range 2}, >0%, {Auth0 Project Plan Range 1}, FIND("MFA",{Auth0 Project Plan Range 1}))
Answers
-
Hi @Mallory B ,
Looking at the FIND Function I notice the example formulas include >0
Makes me wonder if your formula might benefit with it included like so...
FIND("MFA", {Auth0 Project Plan Range 1})>0
as it seems to me the current FIND formula is simply returning the position of the characters within the words?
What do you think?
Hope this helps and that you have a great day,
Jason Albrecht MBA, MBus(AppFin), DipFinMgt
LinkedIn profile - Open to work
-
Thank you Jason. I tried adding that (also in different variations with placement) and it didnt seem to work. I started thinking maybe I was using the wrong function. I tried the AVG and COLLECT but now getting the incorrect argument error. Any other ideas?
=SUMIFS({Auth0 Project Plan Range 6}, {Auth0 Project Plan Range 2}, >0%, {Auth0 Project Plan Range 1}, FIND("MFA",{Auth0 Project Plan Range 1}))
=AVG(COLLECT({Auth0 Project Plan Range 5}, {Auth0 Project Plan Range 2}, >0, {Auth0 Project Plan Range1}, FIND("MFA", {Auth0 Project Plan Range 1})))
-
Hi @Mallory B
SUMIFS is the correct function to use! You're receiving an error due to how you're searching for values above 0%. The % symbol is a text symbol and won't be read properly: you can simply look for > 0, as long as your percentage column is formatted as percents and not manually typed in %.
Additionally, I would recommend using CONTAINS instead of FIND. That will check to see if any cell contains the phrase you're looking for!
Try:
=SUMIFS({Auth0 Project Plan Range 6}, {Auth0 Project Plan Range 2}, >0, {Auth0 Project Plan Range 1}, CONTAINS("MFA", @cell))
See: CONTAINS Function
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!