Sumifs formula with a date

I'm trying to calculate a total value if two criteria are met using the sumifs formula but I keep getting a 0 result. I think the problem has to do with the format of the date field.

The formula is

=SUMIFS({Cost Tracker Testv1.2 Range 5}, {Cost Tracker Testv1.2 Range 4}, "Actual", {Cost Tracker Testv1.2 Range 7}, "01/04/22")

I know it can work as I changed the date "01/04/22" to "test" in the formula and in the original sheet and it returned a value.

The original sheet column is in the data format rather than text/number. I've also tried changing the / to . with no luck. Can anyone help? Thanks

Find more posts tagged with

Accepted answers

Paul Newcome

To use a date in a formula, you have to use the DATE function.

=SUMIFS({Cost Tracker Testv1.2 Range 5}, {Cost Tracker Testv1.2 Range 4}, "Actual", {Cost Tracker Testv1.2 Range 7}, @cell = DATE(2022, 04, 01))

All comments

Paul Newcome

To use a date in a formula, you have to use the DATE function.

=SUMIFS({Cost Tracker Testv1.2 Range 5}, {Cost Tracker Testv1.2 Range 4}, "Actual", {Cost Tracker Testv1.2 Range 7}, @cell = DATE(2022, 04, 01))

Faye Hilbourne

Paul, thank you so much that's worked :)

Paul Newcome

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