SumIF HAS with cell linking
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 😀
Best Answer
-
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
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Answers
-
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
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
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
-
Thank you very much @Mark Cronk - I manage to solve it by using a SUMIF rather than SUMIFS!
-
Excellent. Glad you found a solution. Thank you for contributing to the Community.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!