Using SumIfs with a date qualification

Hi Everyone. I would appreciate any feedback on what I'm doing wrong.

In the formula below, when I hardcode my dates below, I get the correct answer back.

=SUMIFS({Tech Enablement-NCAL - Allocation}, {Tech Enablement-NCAL - Timeline Range 1}, HAS(@cell, $Resource@row), {Tech Enablement-NCAL - StartDate}, <=DATE(2026, 4, 8), {Tech Enablement-NCAL - EndDate}, >=DATE(2026, 4, 8))

When I change the formula to refence a value in Column with a "Date" data type, I get a different answer back which is incorrect. For this example, the value is CalendarDate99 is 4/8/26

=SUMIFS({Tech Enablement-NCAL - Allocation}, {Tech Enablement-NCAL - Timeline Range 1}, HAS(@cell, $Resource@row), {Tech Enablement-NCAL - StartDate}, <=CalendarDate99, {Tech Enablement-NCAL - EndDate}, >=CalendarDate99)

I have absolutely no idea what I'm doing wrong as the there is not any error message being generated. I only get the incorrect value being returned when I use the reference to CalendarDate99 rather than using the Date() function.

Thanks in advance!

Find more posts tagged with

Formulas

Cell linking

Accepted answers

Julie.B

So it was totally my user error Paul! Your hints led me to the solution. While i was focusing so much on my dates, I totally missed that I didn't have the correct value in my other qualifying field. Once I updated that, it worked! I'm all set!

All comments

Paul Newcome

Double check the date in CalendarDate99 is set for 2026 and not 1926.

How exactly are you entering the date in CalendarDate99?

Have you double checked that the CalendarDate column is set as a date type column?

Julie.B

Hi Paul.

I changed the display format of the cell to confirm the date is "2026-04-08". So it's definitely 2026 not 1926.

I typed in the date as 4/8/26.

I confirmed the column is a date type column. I also updated the option to restrict to dates only to be "On" which I understand to mean its restricted to dates only.

Thanks for the suggestions on where I could have introduced errors.

Paul Newcome

Are you able to provide some screenshots for context such as one of each formula, the CalendarDate column to include row 99, the formulas open in the sheet as if you are about to edit them, etc.?

Julie.B

Paul Newcome

Glad you were able to get it sorted.

Happy to help. 👍️

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.