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

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Brian Erdelyi
    Brian Erdelyi ✭✭✭✭

    Thank you! That worked.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!