I need help bucketing some data
I need help combining some data
I have the following columns in sheet-1
· Year-1 Deliverable Date-1 (this is a date column)
· Year-1 Deliverable-1 Amount (this is a currency column)
· Year-1 Deliverable-1 Billing Quarter (this converts the deliverable date into Q1-Q4)
I need to create a formula in sheet-2 that will bucket that info so I can use it in charts.
What I want to quantify from sheet-1 is
· The sum of all the Year-1 Deliverable-1 Amounts IF there is an associated date in the Year-1 Deliverable Date column.
· Then bucket those sums by billing year (i.e., 2019, 2020, etc)
· Then bucket those sums into Q1, Q2, Q3, or Q4
I think that would map out in sheet-2 something like this, but I am open to suggestions;
· Row-1 “2019”, Row-2 “2020”, Row-3 “2021”, etc
· Column-1 “Q1”, Column-2 “Q2”, Column-3 “Q3”, etc
I hope my description makes sense. I have figured out how to do all of the individual pieces but I can sem to get this nested together in a formula that works.
Any help is much appreciated. I am stuck and need help!
Thank you
Best Answer
-
If your sheet is set up like this )column headers in bold)...
YEAR Q1 Q2 Q3 Q4
2019
2020
2021
Then you would first suggest using row 1 of your table to replicate your column headers or put in the associated quarter such as...
YEAR Q1 Q2 Q3 Q4
Q1 Q2 Q3 Q4
2019
2020
2021
Then you can use a formula such as...
=SUMIFS([Amount Column]:[Amount Column], [Date Column]:[Date Column], IFERROR(YEAR(@cell), 0) = YEAR@row, [Quarter Column]:[Quarter Column], [Q1]$1)
Placing that in [Q1]2 will allow you to dragfill for the remaining sections. To get a yearly total, you would just have to drop off the quarterly range/criteria.
=SUMIFS([Amount Column]:[Amount Column], [Date Column]:[Date Column], IFERROR(YEAR(@cell), 0) = YEAR@row)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
If your sheet is set up like this )column headers in bold)...
YEAR Q1 Q2 Q3 Q4
2019
2020
2021
Then you would first suggest using row 1 of your table to replicate your column headers or put in the associated quarter such as...
YEAR Q1 Q2 Q3 Q4
Q1 Q2 Q3 Q4
2019
2020
2021
Then you can use a formula such as...
=SUMIFS([Amount Column]:[Amount Column], [Date Column]:[Date Column], IFERROR(YEAR(@cell), 0) = YEAR@row, [Quarter Column]:[Quarter Column], [Q1]$1)
Placing that in [Q1]2 will allow you to dragfill for the remaining sections. To get a yearly total, you would just have to drop off the quarterly range/criteria.
=SUMIFS([Amount Column]:[Amount Column], [Date Column]:[Date Column], IFERROR(YEAR(@cell), 0) = YEAR@row)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thank you! That worked.
-
Happy to help! 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 377 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!