Sumifs Formula with a Contain

Maria Watters

I am trying to get the sum of hours worked IF the "Office" Column = Administrative Solutions. The Office column is a multi-select dropdown.

=SUMIFS({Project Intake with Form Range 1}, {Project Management Tracker- Discovery to C Range 9}, CONTAINS("Administrative Solutions", @cell"))

Any help would be appreciated! Thank you!

Genevieve P.

Hi @Maria Watters

Since you're looking in a multi-select column, try using the HAS function. It's specifically designed to search for one selection in a multi-select cell.

=SUMIFS({Project Intake with Form Range 1}, {Project Management Tracker- Discovery to C Range 9}, HAS(@cell, "Administrative Solutions"))

Let me know if this works for you! If not, it would be helpful to know what error or issue you're seeing with the formula, and a screen capture of the sheet you're referencing (but please block out any sensitive data).

Cheers,

Genevieve

Genevieve P.

Hi @Maria Watters

Since you're looking in a multi-select column, try using the HAS function. It's specifically designed to search for one selection in a multi-select cell.

=SUMIFS({Project Intake with Form Range 1}, {Project Management Tracker- Discovery to C Range 9}, HAS(@cell, "Administrative Solutions"))

Let me know if this works for you! If not, it would be helpful to know what error or issue you're seeing with the formula, and a screen capture of the sheet you're referencing (but please block out any sensitive data).

Cheers,

Genevieve

Maria Watters

Thank you so much! That did work! What is the difference between HAS and CONTAINS. I believe I have used contains before on a multi-select drop down as well.

Genevieve P.

No problem, I'm glad it worked for you!

CONTAINS will see if a cell contains the text. So, say you were searching for the word "App" but one of the options was also "Apple". It will see the app in Apple and count that as 1.

HAS looks for an EXACT match, start-to-finish. Let me know if that makes sense or not! I'd be happy to post screen captures to illustrate the difference.

Cheers,

Genevieve

Maria Watters

@Genevieve P. Thank you so much! This is VERY Helpful.