SUMIFS with date ranges

Hello,

I'm trying to sum the values (Annual Billing Amount) for anything that falls between 2/22/22 and today but I can't get my formula to work. Not sure what I'm doing incorrectly.

=SUMIFS({Annual Billing Amount}:{Annual Billing Amount}, {Date to Deactivate}:{Date to Deactivate}, AND(>=DATE(2022, 2, 22), <=TODAY()))

Find more posts tagged with

Formulas

Accepted answers

All comments

Jake Gustafson

Try adding another criteria rather than using the AND function.

=SUMIFS([Annual Billing Amount]:[Annual Billing Amount], [Date to Deactivate]:[Date to Deactivate], >=DATE(2022, 2, 22), [Date to Deactivate]:[Date to Deactivate], <TODAY())

In my example sheet I tested in, I didn't use cell references, simply the other columns in the sheet. So not sure on your curly braces there.

deb_63_hydracor

Thanks so much for your response. I tried your formula (uses curly braces instead of brackets), I still got an unparsable error. I have used cross-sheet references before without an issue so not sure what's happening here.

Jake Gustafson

Make sure your Date to Deactivate column is set to Date.

When using curly braces you only need the one reference.

=SUMIFS({Annual Billing Amount}, {Date to Deactivate}, >=DATE(2022, 2, 22), {Date to Deactivate}, <TODAY())

deb_63_hydracor

That worked! Thank you!

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.