SumIF HAS with cell linking

Connor Haw

Hi All,

I have been struggling to find a solution on how to get a Sumif formula to work. Essentially, I want to Sum if a cell contains a specific name, however the name column is multiselect and can have multiple people in it. I have tried both the HAS and CONTAINS formulas but haven't been able to get one to work.

The cells in the formula are also being linked from another sheet.

My current formula is: =SUMIFS({Projects Names}, HAS(@Cell, "Name"), {Projects Hours}). The error I get there is UNPARSEABLE. What am I missing here? This has been the solution I have seen on different threads but it doesn't seem to work for me.

Thank in advance 😀

Mark Cronk

Hi@Connor Haw ,

@cell must have a lowercase "c". Try:

=SUMIFS({Projects Names}, HAS(@cell, "Name"), {Projects Hours})

HAS looks for an exact match. The value in quotes has to be the exact value in your drop down

Work?

Mark

Mark Cronk

Hi@Connor Haw ,

@cell must have a lowercase "c". Try:

=SUMIFS({Projects Names}, HAS(@cell, "Name"), {Projects Hours})

HAS looks for an exact match. The value in quotes has to be the exact value in your drop down

Work?

Mark

Connor Haw

Thanks Mark,

That has changed my error to Incorrect Data Set, I have confirmed the two cell ranges are the same length (they are the entire columns highlighted).

I assume you mean with HAS the value I'm looking for must be the only one in the cell? Or should it still work with multiple options selected?

My formula is now: =SUMIFS({Projects Names}, HAS(@cell, "Name"), {Projects Hours})

Cheers,

Connor

Connor Haw

Thank you very much @Mark Cronk - I manage to solve it by using a SUMIF rather than SUMIFS!

Mark Cronk

Excellent. Glad you found a solution. Thank you for contributing to the Community.

Mark