sumifs as Cross-sheet formulas

edited 12/09/19 in Formulas and Functions

Has anyone had success using the sumifs function as part of cross-sheet formulas? 

I have been giving it a whirl and haven't had much luck. I am not sure if it is even possible, and if is then I'm not sure what I'm missing.



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    It is possible. Are you able to give more detail about your specific need?

  • Thanks Paul.  I have an sheet set up to track payment income and related dates of payment et al. 

    Within that sheet I wanted to track income per week so I added some columns to the left of the sheet to roll-up the data I need, then have this data appear in a Dashboard/Sight.  Now that the formulas work I hide the columns so they're out of sight.

    I'd like to instead have the data on a separate roll-up sheet and keep things cleaner.  

    On my original sheet here is one of the formula examples:

    =SUMIFS([Gross Invoice Amount]:[Gross Invoice Amount], [Date Payment Received]:[Date Payment Received], <=[Payment Week]3, [Date Payment Received]:[Date Payment Received], >=[Payment Week]2)

    Attached is a snapshot of the columns & rows.  Dates are added manually (for now).  

    Ideally I'd like to have these formulas/calculations on a separate roll-up sheet, so I can add add'l Parent/Child organization, sorting etc to my source sheet.  

    Let me know if this sheds enough light.  

    Income Tracker.JPG

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Ah. Ok. When you are building a formula, in the dropdown "help" box you will see a link that says "Reference Another Sheet". Simply click the link then select the appropriate sheet you want to pull the data from. Instead of having "[Gross Invoice Amount]:[Gross Invoice Amount]", it will look more like "{Sheet Name Range 1}". You can do this for each of your ranges whether it be the sum range or a criteria range.


    Does that help, or do you need more detail?

  • Hi Paul - that worked finally - thank you for clarifying and helping out!



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!