Dynamic date range SUMIFS

Intermediate

In Sales Ops reporting, a “last 30 days” metric is a rolling window. Meaning it should update automatically every day without you changing any filters.

In this sheet, you’ll build that rolling window using SUMIFS with a dynamic date threshold based on TODAY().

SUMIFS adds up values that match multiple conditions:

=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2)

Your task:

  1. Review the sales log in A1:E9 (each row is one deal).
  2. Look at the report controls in F2:G4:
    • Channel filter: G2
    • Days lookback (N): G3
  3. In G4, calculate the total Amount for deals that meet both conditions:
    • Channel equals G2
    • Date is within the last N days, using a cutoff of TODAY()-G3

Make sure your result changes automatically if you update G2 or G3.

Need some help?

Hint 1

Use SUMIFS with the Amount column as the sum_range (the values you want to add).

Hint 2

For the date condition, SUMIFS needs a text criteria like `">="&some_date`. Build the cutoff with `TODAY()-G3`.

Hint 3

Double-check your ranges: the Date, Channel, and Amount ranges should cover the same rows (2–9).

Related function(s)
SUMIFSSUMIFCOUNTIFSAVERAGEIFSEOMONTH
More exercises
SUMIFS multiple criteriaIntermediate
Year-over-year comparisonAdvanced
AVERAGEIFS multiple criteriaIntermediate
COUNTIFS multiple criteriaIntermediate
Back to exercises overview

Exercise