Sum of Cells when another Cells Fall In a Certain Date Range

Hello Smartsheet community!

I have a sheet that has a column for a Delivery Date, and another column for the Qty of items to be delivered.

Delivery Date - Column A

Qty of Carrots - Column B

I need to put together Summary Data to know how many Carrots are needed for a specific date range, example the month of June?

Thanks in advance for the assist!

Answers

  • bcwilson.ca
    bcwilson.ca ✭✭✭✭✭
    edited 05/18/21

    =SUMIFS([Qty of Carrots]:[Qty of Carrots], [Delivery Date]:[Delivery Date], AND(@cell >= DATE(2020, 1, 1), @cell <= DATE(2020, 1, 31)))

    The hardest thing about that is with Dates, "SUMIFS" and "AND" statements all together it can do some weird things.. it gets harder if you want to say things like = "Jan"

    This is where cheater columns might help to take the months and years out of the date.. Then you can say things like CheaterMonth = "Jan"

    You could also look at reporting if you are doing forecasting.. the new grouping gets pretty powerful

    That works easier but I had to create a cheater column for the Month so it sorts in the correct order and is readable by month.

    This is using the sum of Qty of Carrots Grouped by the "Cheater Column"

  • Nancy Heater
    Nancy Heater ✭✭✭✭✭

    Thanks for the info. The ask was for creating Summary Data to be used on a Dashboard, so I'll have to keep searching. Thanks!

  • Hi @Nancy Heater

    Charts can now be created using the Grouping and Summary feature in a Report, so the above Reporting suggestion may work for you now!

    See the announcement post here.

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!