Excel track · Exercise 78 of 86 Paid

Totals that multiply, skip and reconcile

Reconcile two routes to the same total and read the ratio as a pass or a fail.

Advanced

Stock list with a value column and a total row to check

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

In D5, total the Line column with SUBTOTAL.

	A	B	C	D
1	Item	Price	Qty	Line
2	pen	10	2	20
3	pad	5	4	20
4	ink	20	3	60
5	Line total

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

In D5, divide the price-times-quantity total by the line total and round it to one decimal place.

	A	B	C	D
1	Item	Price	Qty	Line
2	pen	10	2	20
3	pad	5	4	20
4	ink	20	3	60
5	Line total

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

Reference answer

What it does

The sheet carries two routes to the same number. Column D already holds each row's price times its quantity, and the total under it adds those three lines to 100. SUMPRODUCT rebuilds the same 100 from the prices in B2:B4 and the quantities in C2:C4.

Divide one route by the other and the answer is 1. That is the check: a line typed over, or a row added after the fact, moves the ratio away from 1 while both formulas still return a number. Rounding to one decimal place makes any drift visible at a glance.

Each function starts from a different place on the sheet. SUMPRODUCT walks two ranges in step and multiplies the pairs. SUBTOTAL and AGGREGATE walk a single range, with a function number choosing the calculation, and AGGREGATE adds an options number for what to skip.

Syntax

=SUMPRODUCT(pairs) values a table; =SUBTOTAL(9,range) adds a group; a ratio of the two is a weighted figure

Worked example

On Stock list with a value column and a total row to check, =SUBTOTAL(9,D2:D4) returns 100.

=SUBTOTAL(9,D2:D4)

Common errors

Error codes for SUBTOTAL over a value column and a SUMPRODUCT that reproduces it
Error	What it means and how to fix it
`#DIV/0!`	The subtotal under the line column is zero, so there is nothing to divide by.
`#VALUE!`	The two ranges in SUMPRODUCT are a different height, so the pairs cannot form.
`Wrong ratio`	A line value was typed over, so the two routes disagree and the ratio drifts away from 1.

At work

Checking that a line total agrees with the prices and quantities behind it.