How to write criteria in the SUMIFS function to evaluate a date range

Question

👋
Column A has date values. Column B has numbers. Want to sum those numbers between two dates. If the end date is in cell C1, and I want to sum values for the week ending on that date, looking for the correct criteria1 and criteria2 in the below formula.
=SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2)

=SUMIFS(B:B,A:A,"<=C1",A:A,">(C1-7)") <== that doesn't work.

Tried so many variations. Don't think it's possible. TIA for any suggestions to try.

Answer 1

Use the operators directly with cell references, and calculate the start date inside the criteria string.

Correct version:

=SUMIFS(B:B, A:A, "<="&C1, A:A, ">"&(C1-7))

If you prefer inclusive of both endpoints:

=SUMIFS(B:B, A:A, "<="&C1, A:A, ">="&(C1-7))

---

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin