Articles
Excel SUMIFS Interview Questions: Dates, Wildcards, and Mismatched Ranges
Practice Excel SUMIFS interview questions with timestamp boundaries, literal wildcards, aligned ranges, and a row-level audit of unexpected totals.
An Excel SUMIFS formula returns 50 for February sales, even though a February 28 afternoon sale should bring the total to 120. Another formula uses ranges of the same size and returns 90 without an error. Neither result can be repaired by changing the number format: one uses the wrong time boundary; the other associates values with the wrong rows.
For Excel SUMIFS interview questions, explain the accepted records before the formula. A defensible answer specifies the date interval, literal versus wildcard matching, and the alignment between every criteria range and the sum range. This article develops one small ledger into a debugging method you can narrate while working.
Evidence boundary: Microsoft documentation supports the function semantics. The ledger and questions are original practice exercises; their totals were independently checked with a Python row model, not an Excel calculation engine. They are not an employer assessment or a prediction of interview questions.
For adjacent workbook reasoning, practice the verified Data Analyst questions.
What does SUMIFS actually promise?
SUMIFS adds values whose corresponding rows meet every supplied condition. Its argument order starts with the sum range, followed by criteria-range and criterion pairs. That differs from SUMIF, where the optional sum range comes last. Microsoft also requires matching range dimensions. Microsoft SUMIFS reference
=SUMIFS(sum_range,criteria_range1,criteria1,
criteria_range2,criteria2)
The function does not verify the business relationship between columns. If a date range begins one row later than the amount range, a date can qualify a neighboring amount. Equal dimensions make a formula structurally acceptable; they do not prove record alignment.
A useful opening answer is: “I will sum one amount per accepted transaction, using the date and product from that same transaction. I will audit the selected row IDs independently.” This establishes the unit of analysis before discussing syntax.
Ask whether amounts represent sales, refund events, or already aggregated totals. Summing order totals copied onto several line items inflates revenue even when the formula is technically correct. Establishing grain is part of the answer, not an optional cleanup after getting a number.
Work the February boundary before entering the formula
Create this original ledger in rows 2 through 6. Column A contains real Excel date-time values, B contains product labels, and C contains numeric amounts. The displayed strings below describe those values; they are not instructions to leave dates as text.
| Worksheet row | Date-time, product, amount |
|---|---|
| 2 | February 1, 2026, 00:00; A*; 50 |
| 3 | February 28, 2026, 15:00; A*; 70 |
| 4 | March 1, 2026, 00:00; A*; 40 |
| 5 | February 15, 2026, 12:00; AB; 30 |
| 6 | January 31, 2026, 23:00; A*; 20 |
The task is to sum February transactions for the product whose literal name is A*. Rows 2 and 3 qualify: 50 + 70 = 120. Row 4 belongs to March, row 5 has a different product, and row 6 precedes February.
=SUMIFS($C$2:$C$6,
$A$2:$A$6,">="&DATE(2026,2,1),
$A$2:$A$6,"<"&DATE(2026,3,1),
$B$2:$B$6,"A~*")
This uses a half-open interval: include the start of February, exclude the start of March. Microsoft describes dates as serial values and times as fractional portions of a day. Therefore DATE(2026,2,28) denotes midnight at the start of February 28, not its final moment. DATE reference, NOW reference
Replacing the upper condition with "<="&DATE(2026,2,28) excludes the afternoon transaction. The result becomes 50. A cell formatted to show only the date conceals that time component but does not remove it.
Avoid inventing an end-of-day value such as 23:59:59 unless the source precision and contract demand it. A later fractional-second timestamp could still fall outside that bound. The start of the next period is a clearer boundary and generalizes to daily, weekly, and monthly intervals.
How should you handle dates supplied by someone else?
Check whether the date cells are numeric with ISNUMBER(A2), and inspect a representative cell's underlying value. A text label that looks like a date is not proof of a usable serial date. Similarly, an amount formatted as currency may remain text. Number formatting changes presentation, not the underlying value. Microsoft number formats
Do not fix a failed total by blindly converting every column. Preserve the raw data, confirm the source's date convention, and normalize in a visible helper column. 03/04/2026 is ambiguous without knowing whether the source uses month-first or day-first dates. A plausible conversion into the wrong month creates a harder-to-detect error than an obvious invalid value.
For an explicitly ISO-formatted date string, extracting the year, month, and day into DATE can make the interpretation inspectable. Validate the components separately: constructing a date is not a guarantee that the original string was a valid calendar date. Keep timestamps when the task depends on time, rather than truncating them merely to make an example easier.
Also clarify the reporting time zone before importing timestamped data. The ledger assumes every value already uses the same reporting convention. Excel's displayed dates cannot settle whether an upstream midnight belongs to a UTC day or a customer's local day. That is a source-definition question, not a wildcard or SUMIFS syntax question.
When is an asterisk data rather than a wildcard?
Microsoft documents * as a sequence wildcard, ? as a single-character wildcard, and ~ as the escape prefix. Consequently "A*" means a text pattern beginning with A, whereas "A~*" matches the literal product name containing an asterisk. Microsoft wildcard rules
In the February ledger, the wildcard version includes AB as well as A*. It returns 150, not 120. A candidate who writes the formula without discussing the product label has silently changed the population.
Use three deliberately small tests when explaining a pattern: an exact intended label, a longer similar label, and a near miss. For literal A*, test A*, AB, and B*. For a literal question mark in a code, use ~?; for a literal tilde, use ~~. Do not carry regular-expression assumptions into an Excel wildcard argument.
When a search term comes from a cell, distinguish its role. Is the user supplying a pattern intentionally, or searching for a literal identifier? If literal matching is required, escape the wildcard characters before constructing the criterion. Explain the transformation rather than embedding an opaque formula that no one can audit.
This is especially important for imported product codes. A star can be a real character in the identifier, not a request to include an entire product family. Confirming that contract takes less time than defending an incorrect total later.
Why can equal-sized ranges still produce a wrong answer?
Consider the same formula with both date ranges changed to A3:A7, leaving amounts at C2:C6 and products at B2:B6. All ranges still contain five rows. Assume A7 is blank.
The first amount, 50 from row 2, is now tested against the date from row 3. It qualifies. The second amount, 70, is tested against March 1 from row 4 and fails. The third amount, 40, is tested against February 15 from row 5 and qualifies because its product test still uses B4, which contains A*.
The total becomes 90. This is not a SUMIFS engine bug: the formula describes the wrong pairing. The independently checked row trace exposes why the superficially valid dimensions are insufficient.
Contrast that with a four-row sum range and a five-row criteria range. Microsoft documents a #VALUE! error for mismatched sizes. An error is more visible than the equal-size offset mistake. Do not describe both as the same defect. Microsoft SUMIF/SUMIFS error guidance
Repair the anchors together, then rerun the row-level check. Absolute references prevent unintended shifts when copying a formula; they do not correct a range that was wrong when first entered. A consistently structured Table can make same-row relationships easier to see, but the analyst must still verify that imported columns describe the same records.
How do AND, OR, and overlapping populations differ?
Multiple criteria pairs inside one SUMIFS represent AND: each accepted row satisfies all conditions. Putting East and West as separate equality criteria against the same region column does not express “either region.” No single row can normally equal both labels.
For disjoint labels, adding two SUMIFS results can express OR. The important word is disjoint. Adding a total for labels matching "A*" to a total for literal "AB" counts AB twice because it already belongs to the first population.
Using the ledger's February rows, A* as a wildcard totals 150; a separate AB total adds 30. Their sum is 180, although the union still totals 150. Before proposing a formula, draw or enumerate the overlapping record IDs. A correct union includes each transaction once.
If the requested groups overlap, choose a single row-level inclusion flag or an explicit inclusion-exclusion calculation with an audited intersection. The best answer depends on maintainability and the environment, not on how many functions you can fit into one cell. Explain which rows are counted once and why.
Also state how duplicates should behave. SUMIFS adds matching rows; it does not deduplicate transactions. Two legitimate partial refunds may both belong in a total, while a repeated import of the same refund event may not. Resolve the business key before calculating a distinct-looking summary.
A practical debugging answer you can give aloud
When shown an unexpected result, inspect in this order: the requested population, stored types, criterion meaning, aligned ranges, and the resulting accepted rows. Keep the original formula visible so each change has an explanation.
For the ledger, a concise answer is: “The literal product total should be 120. The 50 result uses midnight on February 28 as its upper bound, excluding a same-day afternoon sale. The 90 result shifts the date ranges one row below the amount range. I will use February 1 inclusive and March 1 exclusive, escape the product's star, and align all five-row ranges.”
Verify more than the final total. Name the two included rows and three exclusions. Then perturb one controlled input: change the February afternoon amount from 70 to 75. The intended total should increase to 125, while moving that transaction to March 1 should exclude it entirely. Restore the fixture after testing.
A zero total is not automatically a failure. It may mean no accepted transactions, incorrectly typed inputs, or a criterion that matches nothing. Report those possibilities separately. Likewise, an error wrapper returning zero can conceal a broken external reference or size mismatch. Diagnose the source error before deciding whether zero is a legitimate result.