Excel track · Exercise 57 of 86 Paid

Sales summary end to end

Total a region with SUMIFS, then answer which region leads with one IF around two totals.

Advanced

Four sales rows tagged by region, summarised by region

Read-only data · screen 1 of 2 · answer in D2

In D2, total the amount column for the North rows only.

	A	B	C	D
1	Region	Rep	Amount	Answer
2	North	Ana	1200
3	South	Bo	850
4	North	Cy	1450
5	East	Di	600

Download this grid (.xlsx) Two tabs: the grid you see, and the answer.

In D2, name the region with the larger total, North or South.

	A	B	C	D
1	Region	Rep	Amount	Answer
2	North	Ana	1200
3	South	Bo	850
4	North	Cy	1450
5	East	Di	600

Download this grid (.xlsx) Two tabs: the grid you see, and the answer.

Reference answer

What it does

SUMIFS names the column to add first, then pairs of range and criterion. That order is the reverse of SUMIF, which takes the range to test, the criterion, then the range to add. Get it wrong and the function returns zero or a total from the wrong column.

Here the amounts in C2:C5 are added where A2:A5 reads North, so rows 2 and 4 count and the answer is 2650. The second screen puts that total beside the South total of 850 inside an IF, and the cell names the region with the larger one.

A summary that stops at totals leaves the reader to compare the figures by eye. The IF answers the question the report was asked, and it costs one extra function on top of two totals that were already there.

Syntax

=SUMIFS(amounts,criteria_range,"North") totals one region; wrap it in IF to compare two

Worked example

On Four sales rows tagged by region, summarised by region, =SUMIFS(C2:C5,A2:A5,"North") returns 2650.

=SUMIFS(C2:C5,A2:A5,"North")

Common errors

Error codes for SUMIFS on an amount and a label column, then a comparison over two of them
Error	What it means and how to fix it
`#VALUE!`	The criteria range and the sum range are different shapes, so the rows cannot be lined up.
`#NAME?`	The function name is misspelled, and Excel returns #NAME?.
`Wrong total`	The criterion points at the rep column instead of the region column, and the answer comes back as 0.

At work

One region's sales pulled out of a flat list, then a sentence naming the region that leads.