Sum Values Between Sheets
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!