Sum Values Between Sheets

raRoque

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.

Nick Korna

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:

Create cross sheet references to work with data in another sheet | Smartsheet Learning Center

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

Each cross sheet reference links to a single sheet. You can create multiple references to pull data from multiple sheets. 

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!