The invoice summary that reports nothing
An invoicing workbook has 320 order rows in 2:321. Column D holds the region exactly as typed ('North', 'South', 'East'), column F the order value pasted from a portal export, and column H the line total, H2 containing =F2*G2 filled down. In the summary block, B3 contains =SUMIFS(F2:F321,D2:D321,"North",F2:F321,">"&$E$1) with 1000 in E1, and it returns 0, even though =COUNTIF(D2:D321,"North") returns 84. Several cells in column F show a small green triangle. The printed lines add to 12,345.67 while the total cell reads 12,345.68. Row 2 of the summary holds the region labels in B2:D2, and the same figures are now needed for East and West across four quarter rows.
1. B3 returns 0 while COUNTIF finds 84 North orders. List the plausible causes, state how you would confirm each, and say which one the evidence rules out.
Read the worked answer
COUNTIF only reads column D, so 84 proves the region labels are clean. The fault lies on the sum side.
- Column F holds text. SUMIFS treats text as non-numeric and adds nothing, so the result is 0 rather than an error. This fits the green triangles. Confirm with =COUNT(F2:F321) - a figure far below 320, possibly 0 - and =SUMPRODUCT(--ISNUMBER(F2:F321)) for the true count.
- Mismatched range sizes. =SUMIFS(F2:F321,D2:D320,...) returns #VALUE!, not 0, so this is ruled out by the symptom.
- Badly built criterion. ">"&$E$1 is correct; ">E1", or a stray space inside the quotes, legitimately matches zero rows. Confirm by running =SUMIFS(F2:F321,D2:D321,"North") with no value criterion: if that is also 0, the problem is in F, not in the criteria.
Fix the data type before touching the formula - a formula rewrite cannot rescue text values.
2. Rewrite B3 so it can be copied across to C3 and D3 and down four quarter rows, always reading the same data block and the same threshold. Justify every $ in your version.
Read the worked answer
=SUMIFS($F$2:$F$321,$D$2:$D$321,B$2,$F$2:$F$321,">"&$E$1)
- $F$2:$F$321 and $D$2:$D$321 - the data block never moves. Without the $, copying one column right would silently read G and E, which is the classic error: totals that look plausible but measure the wrong column.
- B$2 - row locked, column free. Dragging right picks up C2 ('East') and D2 ('West'); dragging down stays on row 2 and does not slide onto the data.
- $E$1 - absolute threshold. If it were E1, the second row would read E2, an empty cell, and the criterion would become ">0", so every row would pass and the total would balloon.
Verification: press Ctrl+` to show all formulas, copy to the far corner, and confirm each reference still points where you intend. Trace Precedents on one copied cell catches drift quickly.
3. The printed lines add to 12,345.67 but the total cell shows 12,345.68. Explain the discrepancy and give the correct fix, naming a wrong fix people commonly apply.
Read the worked answer
Cause. H2 stores the full product - for example 3 x 4.115 = 12.345 - and the two-decimal display is appearance only. The total sums the stored values (12,345.678) and displays 12,345.68, while a reader adding the printed lines reaches 12,345.67. The workbook is not wrong, but the document is: it prints a set of figures that do not add up.
Correct fix. Put =ROUND(F2*G2,2) in H2 so the stored value equals the printed value. Now the sum of the lines is exactly the total, and any auditor can add the column.
Wrong fixes. Wrapping the totals in ROUND only hides half the problem and leaves the lines unrounded. Setting the cells to two decimals and assuming that rounds is the other: formatting is cosmetic and travels with the file while the value does not change.
Caution. Excel's ROUND rounds halves away from zero (0.125 becomes 0.13), unlike banker's rounding in some systems, so agree the convention with whoever checks the invoice.