SUMIFS with Reference Columns?

PerDeSi · 12/12/23

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)

Paul Newcome · 12/21/23

You are setting up your cross sheet references incorrectly.

You have

[{Range}]:[{Range}]

You should have

{Range}

.

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

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

Andy S. · 12/12/23

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 · 12/12/23

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. · 12/12/23

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 · 12/13/23

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 · 12/20/23

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"