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

Nancy Heater

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!

bcwilson.ca

=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

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!

Genevieve P.

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