SUMIFS interview question: summarise sales by region

Answer it with one SUMIFS formula, written once and copied across the whole summary: =SUMIFS($D$2:$D$9,$A$2:$A$9,$F2,$B$2:$B$9,G$1). It adds the amounts in D for the rows whose region matches the label on its row and whose product matches the label above its column. The $ signs are the real test: they decide whether the formula still points at the right labels after you copy it.

The question

“This is last month’s sales export: one row per deal. Build me a summary with regions down the side and products across the top, showing total sales in each box. No pivot tables, please: I want to see the formulas.”

Variations include “how many deals over 1,000 did each region close” (COUNTIFS) or “total sales for the region I pick from a list” (SUMIFS pointed at a cell).

What they’re really testing

• SUMIFS’s argument order. The range to add comes first, then a range and a criterion for each condition.
• Mixed references. One formula that copies correctly across a grid, instead of a different formula typed into each box.
• Criteria from cells. The labels on the sheet drive the formula, so changing a label changes the total.
• That you check your work. A summary whose total doesn’t match the raw data has a hole in it.

The worked answer

The deals are in A1:D9 and the summary grid in F1:H4. The formula in G2 is copied to every box:

G2fx=SUMIFS($D$2:$D$9,$A$2:$A$9,$F2,$B$2:$B$9,G$1)

Row	A	B	C	D	E	F	G	H
1	Region	Product	Rep	Amount			Laptop	Monitor
2	North	Laptop	Kim	1,200		North	2,100	450
3	South	Monitor	Raj	300		South	1,100	800
4	North	Monitor	Kim	450		West	1,350	250
5	West	Laptop	Ade	1,350
6	South	Laptop	Raj	1,100
7	North	Laptop	Lu	900
8	West	Monitor	Ade	250
9	South	Monitor	Lu	500

Read it as “add up D2:D9 where the region is the label in this row’s column F, and the product is the label in this column’s row 1”. The $ signs do the rest:

Reference	Locked	When copied across	When copied down
$D$2:$D$9, $A$2:$A$9, $B$2:$B$9	Column and row	Stays	Stays
$F2	Column only	Stays on F	Moves to F3, F4
G$1	Row only	Moves to H1	Stays on row 1

Then prove it. The six boxes should add up to the same total as the raw amounts. Say this out loud in the interview: it’s what a careful analyst does before sending anything.

A difference of 0 means every deal landed in exactly one box. A negative number means a region or product is missing from the labels, or spelt differently in the data; a positive one means a label appears twice in the grid.

The $ signs, and what happens without them

Write =SUMIFS(D2:D9,A2:A9,F2,B2:B9,G1) in G2 and it’s right in that one box. Copy it to H3 and every reference moves one row down and one column right: the amounts become E3:E10, an empty column, the region G3, a number, and the product H2, another number. Nothing matches, so the box shows 0 instead of South’s monitor sales:

Zero is a believable number in a sales report, which is why this mistake survives until someone checks the total.

The COUNTIFS version

“How many deals over 1,000?” is COUNTIFS: the same pairs of range and criterion, without a range to add:

Add a pair for each extra condition, such as the region: =COUNTIFS($A$2:$A$9,$F2,$D$2:$D$9,">1000"). The comparison goes in quotes. To compare with a number in a cell, join them: ">"&J1.

Common wrong answers, and why they fail

1. SUMIF’s order inside SUMIFS. SUMIF puts the range to add last, SUMIFS first. Mixing them up adds the wrong column or returns an error.
2. One condition only. SUMIF on the region totals every product in it. Each condition needs its own pair.
3. Typed criteria. "North" inside the formula works in one box and stops following the labels.
4. Missing $ signs. Right in the first box, wrong or 0 everywhere it’s copied.
5. COUNTIFS when they asked for a total. It counts the deals instead of adding their amounts.
6. Ranges of different sizes. Every range in SUMIFS must have the same number of rows, or it returns #VALUE!.

Practise it: total one region and one product

A smaller table with the region and product typed into cells, so the grid formula above won’t fit. We check your total, then change the region, the product and one of the amounts.

Your task: In F3, total the amounts for the region in F1 and the product in F2.

Graded live

F3fx

Row	A	B	C	D	E	F
1	Region	Product	Amount		Region	East
2	East	Chairs	420		Product	Desks
3	West	Desks	980		Total
4	East	Desks	760
5	East	Desks	1,150
6	West	Chairs	300
7	North	Desks	640
8	East	Chairs	210

Write the formula in the bar above, then press Check or Enter.

Hint

SUMIFS takes the range to add first (C2:C8), then pairs: the regions with F1, and the products with F2.

Solution

=SUMIFS(C2:C8,A2:A8,F1,B2:B8,F2) Any formula that returns the same result passes.

Check

If they ask about SUMPRODUCT

SUMPRODUCT can do the same with arrays, and handles conditions SUMIFS can’t, such as a test on the month of a date. For a plain summary by labels, SUMIFS is easier to read and to check. See SUMPRODUCT for when it’s the better tool.

How to answer it in the interview

“One SUMIFS, written in the first box and copied across the grid: the amounts first, then the regions against the row label and the products against the column label, with the data ranges locked and the labels half-locked. Then I check that the grid adds up to the raw total.” More questions like this one: Excel interview questions.