SUMIFS Formula with Date Column

Hello All,

I am using this formula "

=SUMIFS({RCX- Kit Forecast - 2025 Range 1}, {RCX- Kit Forecast - 2025 Range 2}, ">=" + TODAY(), {RCX- Kit Forecast - 2025 Range 2}, "<=" + (TODAY() + 5))

"
Range 1 is the number column
Range 2 is a date column

But the value is coming as zero

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

In Smartsheet, the operators are treated a little differently than they are in Excel.

=SUMIFS({RCX- Kit Forecast - 2025 Range 1}, {RCX- Kit Forecast - 2025 Range 2}, @cell >= TODAY(), {RCX- Kit Forecast - 2025 Range 2}, @cell <= TODAY(5))

You can put whatever number you want added (positive numbers) or subtracted (negative numbers) from today's date insidet the TODAY function.

All comments

Jgorsich

Two thoughts - first, you probably want countif rather than sumif. Second, concatenating date logic never really works for me - doing this in a test I was able to make it work with a date cell formula of =today()+5 but not with it embedded as a formula.

Here was the test function I wrote - dates14 was "=today()+5" and dates3:date10 was just filled with a sample of dates.

=COUNTIF(Dates3:Dates10, >=Dates14)

When I've needed it as a column formula I've entered the "=today()+5" as a sheet summary cell.

Edited: The answers below are better than mine. If anyone comes across this in the future, see below.

dojones

This formula works

=SUMIFS([Kits Needed]@row , [Expected Request Data]@row , >=TODAY(), [Expected Request Data]@row , <=(TODAY() + 5))

Change your formula to match:

=SUMIFS({RCX- Kit Forecast - 2025 Range 1}, {RCX- Kit Forecast - 2025 Range 2}, >= TODAY(), {RCX- Kit Forecast - 2025 Range 2}, <= (TODAY() + 5))

Paul Newcome

In Smartsheet, the operators are treated a little differently than they are in Excel.

=SUMIFS({RCX- Kit Forecast - 2025 Range 1}, {RCX- Kit Forecast - 2025 Range 2}, @cell >= TODAY(), {RCX- Kit Forecast - 2025 Range 2}, @cell <= TODAY(5))

You can put whatever number you want added (positive numbers) or subtracted (negative numbers) from today's date insidet the TODAY function.

Jgorsich

I had NOT been able to figure that out, I'm glad you replied!

Ozu

Thanks Paul this works exactly as I wanted, thanks.

Paul Newcome

@Jgorsich and @Ozu Happy to help. 👍️

Here's another quick tip… Want to cut down on the number of times you have to type out a {cross sheet reference} whether it be for easier to read formulas or overall sheet performance? You can use the AND function to combine criteria for the same range.

In this case, you used the same {Reference To Date Column} twice. You can also write it once and use an AND function like so:

=SUMIFS({RCX- Kit Forecast - 2025 Range 1}, {RCX- Kit Forecast - 2025 Range 2}, AND(@cell >= TODAY(), @cell <= TODAY(5)))

It doesn't make a huge difference in this particular case, but longer / more complex formulas can get a bit unwieldy. It also helps consolidate the criteria for the range so it is easier to tweak later down the road if needed.

Jgorsich

That is pretty great to know, thanks for the tip!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.