Sheet Summary Formula with multiple criteria

Dana G

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!

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], ="")

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. 👍️