Using SumIFs and Find referencing other sheets

Mallory B

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}))

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?

Mallory B

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})))

Genevieve P.

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