SUMIFS with Reference Columns?

Hi Everyone,

I am stuck on a scenario that I need assistance with.

I have a sheet to track project tasks. (screen # 1). In this sheet, I have a formula to calculate hours per person per month and it works great.

I would like to move this formula to a helper-sheet (screen # 2) so I can run reports on it.

However, when I attempt to run the same formula (and adjusting the column names), I can't get the formula to populate the information properly in the helper-sheet. Is there a restriction or a special way to run the SUMIFs formula with referenced columns?

Thank you in advance for any guidance.

Screenshot # 1 (Src Sheet)

Screenshot # 2 (Helper-Sheet)

Find more posts tagged with

Accepted answers

Paul Newcome

You are setting up your cross sheet references incorrectly.

You have

[{Range}]:[{Range}]

You should have

{Range}

=SUMIFS(~~[{Range}]:[{Range}]~~, ..................

=SUMIFS({Range}, ........................

All comments

Andy S.

It might be easier just to use an Index-match formula to mirror the result of the formula in your primary sheet.

=index({reference to actual hours worked column in primary sheet}, match([assigned to]@row, {reference to assigned to column in primary sheet}, 0))

I hope I'm understanding your desired result correctly and that helps. Let me know!

PerDeSi

https://community.smartsheet.com/discussion/comment/409185#Comment_409185

I appreciate the feedback Andy!

However, I am looking keep the Src (Project) sheet limited to specific columns (for template purposes) and use the helper-sheet to do all of my calculations and to generate reports.

I am surprised the formula isn't working in the helper-sheet as the only variable is that the columns are referencing data from another sheet... :-(

Andy S.

https://community.smartsheet.com/discussion/comment/409190#Comment_409190

It might just be a syntax error. It seems like your Month and year formulas ought to have [Actual End Date]@cell rather than just @cell. If that doesn't work, I would test each individual piece of your formula to see which part is having the issue.

Paul Newcome

No. You need the @cell references in the MONTH and YEAR functions. That isn't the issue.

Exactly how are you bringing everything over into your helper sheet, and is there a reason you are bringing everything over and running your SUMIFS there as opposed to running your SUMIFS with cross sheet references?

PerDeSi

I tried using cross sheet reference (preferred method) but unfortunatly I can't get it to work. The formula works in the original sheet but not in the helper sheet, see below:

Main Sheet: "Project Plan"

Helper Sheet:

Thank you in advance for your assistance.

Paul Newcome

You are setting up your cross sheet references incorrectly.

You have

[{Range}]:[{Range}]

You should have

{Range}

=SUMIFS(~~[{Range}]:[{Range}]~~, ..................

=SUMIFS({Range}, ........................

PerDeSi

Sorry, forgot to reply to this.

@Paul Newcome that did it, thank you for the assistance!

Paul Newcome

Happy to help. 👍️

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.