Sum Values Between Sheets

Options

I am trying to gather the total sum of revenue from a list of events based on singular dates. My main sheet has all events listed with date and revenue. I have a 2nd sheet I am looking to pull the revenue from sheet 1 based on a date range to track the total sum by week (i.e. Week 1 (01/01/23 - 01/07/23) Total Revenue: $1,000.00). I am having trouble pulling the information over to the new sheet between the date ranges.

Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Options

    Hi @raRoque,

    This is very doable with some cross sheet references.

    For example, here is some sample data:

    The WEEKNUMBER from formula column is using:

    =WEEKNUMBER(Date@row)

    As you want your week 1 to be from Jan 1st to Jan 7th, this needs amending slightly:

    =WEEKNUMBER(Date@row + 1)

    On your 2nd sheet, you can then use a formula with cross references:

    =SUMIF({Amended week number}, [Week number]@row, {Revenue})

    If you're not sure how to do cross sheet references then this article should start you off:

    Sample output from the formula:

    Alternatively you could add date start/end columns on your 2nd sheet and use a SUMIFS formula:

    =SUMIFS({Revenue}, {Event Date}, >=[Date Start]@row, {Event Date}, <=[Date End]@row)

    Pick whichever option you prefer!

    Hope this helps or at least gives you some ideas. If you've any problems/queries then just ask!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!