A SUMIFS that is too high and a margin column full of errors
A sales workbook holds 5,000 rows in A2:E5001: A order date, B region, C rep, D revenue, E cost. The dates were pasted from a web page, so some are left-aligned text such as 31/03/2025 while others are real dates. F1 holds North, F2 holds 01/01/2025 and F3 holds 31/03/2025. You write =SUMIFS(D:D,B:B,F1,A:A,">="&F2) and get 412,900, but your manager expects 268,400 for North in the first quarter. Column G contains =(D2-E2)/D2 and shows #DIV/0! wherever revenue is zero. Column H contains =D2*0.2, formatted to zero decimal places; the total of column H is 1,234.56 higher than the sum of the numbers displayed on screen.
1. Diagnose the 412,900 result and list the checks and corrections in order.
Read the worked answer
The formula has no upper date bound. It sums North from 1 January onwards, not North in Q1, so every later North order is included. That alone explains a figure well above 268,400.
Corrections and checks:
- Add the end date: =SUMIFS($D$2:$D$5001,$B$2:$B$5001,$F$1,$A$2:$A$5001,">="&$F$2,$A$2:$A$5001,"<="&$F$3).
- Test the date types. Put =ISNUMBER(A2) beside a sample. Text dates are not true dates: comparisons with them behave unpredictably, and some will pass a >= test simply because text sorts above numbers. Convert the column with Data > Text to Columns > Date, or a helper column using =DATEVALUE(A2), then paste values back.
- Check the region text. Trailing spaces from the web paste mean North in B does not equal North in F1. Test with =LEN(B2) and clean with TRIM.
- Use fixed ranges rather than whole columns. Whole-column references work, but they slow recalculation and hide mistakes such as a stray value in row 6000.
- Verify the criteria cells. If F2 or F3 is text, ">="&F2 builds a text criterion. Re-enter the dates or use =DATE(2025,1,1) and =DATE(2025,3,31).
2. How should the #DIV/0! errors in column G be handled, and what should be avoided?
Read the worked answer
#DIV/0! occurs when D2 is zero or blank, because the formula divides by it. The fix depends on what the reader should see:
- Show a blank for zero-revenue rows: =IF(D2=0,"",(D2-E2)/D2).
- Show a meaningful label: =IF(D2=0,"No revenue",(D2-E2)/D2).
- Wrap the whole expression: =IFERROR((D2-E2)/D2,""). This is shorter but catches every error, including #REF! and #VALUE!, which you might need to see.
What to avoid:
- Do not hide errors with white font or a matching fill. The value is still an error and will break any downstream total, chart or PivotTable.
- Do not replace errors with 0 unless zero is genuinely correct; an average margin will then be dragged down by rows that had no revenue.
- For aggregating margin, use =AVERAGEIF(D2:D5001,">0",G2:G5001) rather than AVERAGE(G:G), so the blanks or labels are excluded.
After cleaning, sort or filter to confirm no #DIV/0! remains.
3. Explain the 1,234.56 discrepancy in column H and how to make the displayed values and the total agree.
Read the worked answer
The formatting to zero decimal places changes only what is shown. Each cell still stores the full-precision result, for example 24.58, which displays as 25. The total sums the stored values, so it can differ from the sum of the rounded numbers on screen. This is a display issue, not a calculation error.
Options:
- If the line items must reconcile to the total at the displayed precision, round in the formula: =ROUND(D2*0.2,2), then copy and paste values if the figures must be fixed. The total will then equal the sum of the rounded lines.
- If only the report appearance matters, keep the raw values and show the total with the same number format. Add a note that the column total is calculated from unrounded figures.
- For taxes or fees, check the rounding rule: ROUND, ROUNDUP and ROUNDDOWN give different totals across 5,000 rows, and accounting practice may require rounding at the invoice level, not the line level.
Summing the displayed values manually and comparing with the cell total is a useful audit check: any persistent difference points to unrounded stored values or hidden decimal places.