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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    If your sheet is set up like this )column headers in bold)...

    YEAR Q1 Q2 Q3 Q4




    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




    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)


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!