Summing Data From Another Sheet

Koby, Laura
Koby, Laura ✭✭
edited 04/16/20 in Formulas and Functions

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

image.png


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!

Tags:
· Share on FacebookShare on Twitter

Answers

  • Koby, Laura
    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 ...

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee Admin

    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

    · Share on FacebookShare on Twitter
  • Koby, Laura
    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.

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee Admin

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

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!