SUMIFS and Cross-Sheet Reference

I am attempting to write a formula in a new sheet that sums the effort estimate for each individual by month.I feel like I'm very close as I no longer get an "unparseable" error, but my formula is returning a value of 0 when it should not. In the formula example below, I'm attempting to pull in the total of estimated hours for "Misty Lastname" for the month of January "1". Any help is appreciated!

=IFERROR(SUMIFS({Effort Estimated Column}, {Assigned To Column}, HAS({Assigned To Column}, "Misty Lastname"), {End Month Column}, "1"), "")

smartsheet sumif formula help.png

samrtsheet2.png

Find more posts tagged with

Accepted answers

Misty

Hi @Andrée Starå . Thank you for your suggestion. Unfortunately it did not work. The formula still returned a 0 value.

I researched a bit more and tried using the @cell feature and was able to get it to work (hooray!!!) with this formula:

=IFERROR(SUMIFS({Effort Estimated Column}, {Assigned To Column}, HAS(@cell, "Misty Lastname"), {End Month Column}, "1"), "")

All comments

Andrée Starå

Hi @Misty

I hope you're well and safe!

Try something like this.

=IFERROR(SUMIFS({Effort Estimated Column}, {Assigned To Column}, HAS({Assigned To Column}, "Misty Lastname"), {End Month Column}, 1), "")

Did that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Misty

Hi @Andrée Starå . Thank you for your suggestion. Unfortunately it did not work. The formula still returned a 0 value.

I researched a bit more and tried using the @cell feature and was able to get it to work (hooray!!!) with this formula:

=IFERROR(SUMIFS({Effort Estimated Column}, {Assigned To Column}, HAS(@cell, "Misty Lastname"), {End Month Column}, "1"), "")

Andrée Starå

@Misty

Happy to help!

Glad you got it working!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.