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

  • Jason Albrecht
    Jason Albrecht ✭✭✭✭✭✭

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!