Summing Data From Another Sheet

Koby, Laura

I have a resource tracker that is set up to sum hours by resource using the =SUM(CHILDREN()) Function.

I also need to be able to sum hours by Project Code (Column 4), and Classification (Column 8) by Month.

Can someone advise on the best way to approach this? My first thought was to use a SUMIFS formula --

=SUMIFS([Jan 2020 Actual]:[Jan 2020 Actual], [Tenrox Code]:[Tenrox Code], "10110", Classification:Classification, "<>Temp Staff").

This works in the Sheet Summary, but I really need the values to appear on a sheet so I can roll them into a report. Is it possible to reference another sheet via a formula? Is VLOOKUP a better approach?

Thanks for any insight!

Koby, Laura

As a followup, I created a sheet and used the above SUMIFS formula, which seemed to be achieving what I wanted it to. Except after I enter it for a few months successfully, I get an Incorrect Argument Set error anywhere I put the formula (where it was initially returning sum values). Not sure what this is ...

Genevieve P.

Hi Laura,

It looks like you've put the indication to exclude Temp Staff (<>) within the quotes. The quotes should only go around the text, like so:

=SUMIFS([Jan 2020 Actual]:[Jan 2020 Actual], [Tenrox Code]:[Tenrox Code], "10110", Classification:Classification, <> "Temp Staff")

In regards to using this in a different sheet, this is definitely possible! You would just need to replace any of the referenced columns with Cross Sheet References. For example:

=SUMIFS({Jan 2020 Actual in other sheet}, {Tenrox Code in other sheet}, "10110", {Classification in other sheet}, <> "Temp Staff")

Here's a Help Center article on how to use Cross Sheet References: https://help.smartsheet.com/learning-track/smartsheet-advanced/cross-sheet-formulas

Let me know if this works for you, or if you need any additional help!

Cheers,

Genevieve

Koby, Laura

Thanks Genvieve - I figured it out! FYI there seems to be a bug that throws the Incorrect Argument Set error even when the formula is correct; strangely refreshing the sheet fixes it.

Genevieve P.

Oh very strange! If this keeps happening you may want to report it to our Support Team (here).