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)
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)
-
Thank you! That worked.
-
Happy to help! 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!