I need help bucketing some data

Brian Erdelyi

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

Paul Newcome

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)