SUMIFS or SUM(COLLECT) With Cross-sheet Reference

Colin B

I'm trying to total up hours from a timesheet for each individual across multiple projects. I've tried the following two formulas but both are returning 0.

SUMIFS({Timesheet Hours}, {Timesheet Name}, [Resource Name]@row, {Timesheet SOW}, SOW@row)

=SUM(COLLECT({Timesheet Hours}, {Timesheet Name}, [Resource Name]@row, {Timesheet SOW}, SOW@row))

Colin B

Found my issue. I needed an = before [Resource Name]@row and SOW@row respectively.

Michael Culley

I had a similar issue where I needed to convert timesheet hours to a value. Basically whatever formula you have calculating the timesheet hours surround it with a =value(.....).

Does that fix the issue?

Michael Culley

Then use the sumifs formula that you already made.

Colin B

https://community.smartsheet.com/discussion/comment/347964#Comment_347964

Unfortunately not. The Hours and SOW columns from each sheet are both Text/Number fields while the Name columns are Contact List.

Colin B

Found my issue. I needed an = before [Resource Name]@row and SOW@row respectively.

Michael Culley

Ah good catch