I need help bucketing some data
I need help combining some data
I have the following columns in sheet1
· Year1 Deliverable Date1 (this is a date column)
· Year1 Deliverable1 Amount (this is a currency column)
· Year1 Deliverable1 Billing Quarter (this converts the deliverable date into Q1Q4)
I need to create a formula in sheet2 that will bucket that info so I can use it in charts.
What I want to quantify from sheet1 is
· The sum of all the Year1 Deliverable1 Amounts IF there is an associated date in the Year1 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 sheet2 something like this, but I am open to suggestions;
· Row1 “2019”, Row2 “2020”, Row3 “2021”, etc
· Column1 “Q1”, Column2 “Q2”, Column3 “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.

Help Article Resources
Categories
Check out the Formula Handbook template!