On the 'Financials' worksheet, in cell C2, enter a formula that sums the 'Actual Sales' column for all sales that occurred between 1/1/2019 and 1/31/2019, inclusive.

Explanation: Using SUMIFS to Sum Sales Within a Date Range Overall Goal You need to conditionally sum a column of sales figures based on two date conditions - a start date and an end date. This requires SUMIFS (not SUMIF), because SUMIFS supports multiple criteria, while SUMIF on

Create advanced formulas and macros

Question

On the 'Financials' worksheet, in cell C2, enter a formula that sums the 'Actual Sales' column for all sales that occurred between 1/1/2019 and 1/31/2019, inclusive.

Explanation

Explanation: Using SUMIFS to Sum Sales Within a Date Range

Overall Goal

You need to conditionally sum a column of sales figures based on two date conditions - a start date and an end date. This requires SUMIFS (not SUMIF), because SUMIFS supports multiple criteria, while SUMIF only handles one. Whenever a sum depends on more than one condition, SUMIFS is the correct tool.

The Formula

=SUMIFS([Actual Sales range], [Date range], ">="&DATE(2019,1,1), [Date range], "<="&DATE(2019,1,31))

A concrete example (assuming dates are in column A and sales in column B, with data starting in row 2):

=SUMIFS(B:B, A:A, ">="&DATE(2019,1,1), A:A, "<="&DATE(2019,1,31))

Step-by-Step Breakdown

Step 1 - Navigate to cell C2 on the Financials worksheet. The problem specifies exactly where the formula lives. Entering it anywhere else gives the wrong result or breaks references.

Step 2 - Choose SUMIFS, not SUMIF. SUMIF signature: SUMIF(range, criteria, sum_range) - one condition only. SUMIFS signature: SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...) - multiple conditions. If you used SUMIF here, you could only apply one date bound, meaning you'd sum too much (e.g., all sales from 1/1/2019 onward with no upper limit).

Step 3 - Specify the sum range first. In SUMIFS, the sum range comes first (opposite of SUMIF). This is the 'Actual Sales' column. Getting the argument order wrong causes Excel to sum the wrong column or return an error.

Step 4 - Apply the lower bound (>="&DATE(2019,1,1)). The >= operator makes the range inclusive of January 1st. Using > instead would exclude 1/1/2019, which violates the "inclusive" requirement. Using DATE(2019,1,1) is safer than typing "1/1/2019" as a string, because Excel stores dates as serial numbers - DATE() guarantees a numeric match rather than relying on locale-specific text parsing.

Step 5 - Apply the upper bound (<="&DATE(2019,1,31)). Same logic: <= keeps January 31st inclusive. Using < would exclude the last day of the month. Both criteria reference the same date column - this is what makes it a true "between" filter.

Step 6 - Concatenate the operator and date with &. You cannot write >=DATE(2019,1,1) directly inside SUMIFS. The comparison operator must be a text string, so you wrap it in quotes and join it to the date value with &. Omitting the & causes a syntax error.

What Goes Wrong If You Skip Steps

Mistake	Result
Using `SUMIF` instead of `SUMIFS`	Can only apply one date condition; over-counts sales
Swapping sum_range and criteria_range	Sums the date column instead of sales
Using `>` instead of `>=` (or `<` instead of `<=`)	Excludes the boundary dates; under-counts sales
Writing `">=1/1/2019"` as a plain string	May fail or misinterpret dates in non-US locales
Forgetting `&` before `DATE()`	Formula syntax error

Memory Tip

Think of SUMIFS as "SUM IF all conditions are true." The structure is always: sum what, look where, match what - repeated for each condition.

SUMIFS( WHAT to sum, WHERE to check #1, WHAT to match #1, WHERE to check #2, WHAT to match #2 )

For date ranges specifically, remember: two conditions = two date columns references - one with >=, one with <=.

Topics

#Formulas#SUMIFS function#Conditional aggregation#Date criteria

Community Discussion

No community discussion yet for this question.

Full MO-201 Practice