Sheet Summary Formula with multiple criteria

Hello! I'm trying to create a sheet summary formula to count number of items due in the next week. Here's what I have so far, but it's returning the error "#INVALID OPERATION"

=SUMIFS(Counter:Counter, [Due Date INTERNAL]:[Due Date INTERNAL], AND(>=TODAY(), <TODAY(7)), [Submission Date]:[Submission Date], ="")

The "Counter" column has the number of items due. I'm hoping the formula will look in the "Due Date INTERNAL" column for dates between today and 7 days from now, that haven't been submitted yet, then sum the amount in "Counter."

Thank you for looking!

Find more posts tagged with

Accepted answers

Paul Newcome

You would need "@cell" references inside of the AND function:

=SUMIFS(Counter:Counter, [Due Date INTERNAL]:[Due Date INTERNAL], AND(@cell>=TODAY(), @cell<TODAY(7)), [Submission Date]:[Submission Date], ="")

All comments

Paul Newcome

You would need "@cell" references inside of the AND function:

=SUMIFS(Counter:Counter, [Due Date INTERNAL]:[Due Date INTERNAL], AND(@cell>=TODAY(), @cell<TODAY(7)), [Submission Date]:[Submission Date], ="")

Dana G

Aha! A small but important detail. Thanks so much Paul!

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