The stationery shop invoice that will not total
Ritu runs a stationery shop in Tezpur and records stock and sales in a spreadsheet. Her columns are A Item, B Rate, C Qty, D Discount %, E Taxable Value, F Category, G GST % and H Amount. In row 2 she entered =B2C2(1-D2/100) in E2. For G2 she used =VLOOKUP(F2,J2:K3,2,FALSE) and for H2 =E2*(1+G2/100). She copied E2:H2 down to row 41. Her GST rate table sits in J2:K3, with 5% and 12% rates. Several H cells now show 0, the grand total is lower than her manual register, and some Rate cells copied from a supplier PDF carry a small green triangle. She has 40 rows, a 6 p.m. deadline to email the invoice summary, and needs one formula set she can copy safely.
1. Explain the two likely causes of the wrong totals and give corrected formulas for E2, G2 and H2 that Ritu can copy down safely.
Read the worked answer
The green triangle is the spreadsheet warning that a number is stored as text, often because a PDF copy carried a leading apostrophe, an ordinary space or a non-breaking space. Arithmetic such as B2*C2 may coerce some of these values, but SUM ignores text, so the grand total silently drops. The second fault is the relative range J2:K3 in the VLOOKUP: copied down, it becomes J3:K4, J4:K5 and so on, so rows below the rate table return #N/A and H becomes 0 or an error.
Clean the values with TRIM and VALUE. For row 2: E2 = ROUND(VALUE(TRIM(B2))VALUE(TRIM(C2))(1-VALUE(TRIM(D2))/100),2); G2 = IFERROR(VLOOKUP(TRIM(F2),$J$2:$K$3,2,FALSE),0); H2 = ROUND(E2*(1+G2/100),2). The dollar signs lock the rate table. Test row 2 and the row just below the table before copying to row 41.
2. What checks and controls should Ritu apply before she emails the summary at 6 p.m.?
Read the worked answer
She should reconcile rather than trust the total. Pick three rows, including one 5% and one 12% item, and recompute them by hand or calculator. Check that no H cell shows #N/A, 0 or a green triangle, and compare the spreadsheet grand total with the manual register. If they differ, find the exact row difference instead of adjusting the total to match.
Add data validation: Qty as a whole number of 0 or more, Discount as a decimal between 0 and 20, and Category as a list matching J2:J3. Format Rate and Amount as currency with two decimals, freeze the header row, and save a dated copy before editing. For the email, attach a PDF summary and the spreadsheet, use a clear subject line and file name, and leave out supplier cost prices if the wholesaler only needs sales totals.
3. The wholesaler asks for a category-wise total. Ritu could sort the rows and add subtotals manually. Give a better approach and explain the risk of the manual method.
Read the worked answer
A SUMIF formula is safer: for each category in the rate table, use =SUMIF($F$2:$F$41,J2,$H$2:$H$41). It reads the live data, keeps the invoice order intact and updates when a row changes. Put the result in a small summary block with a total row that should equal the grand total.
Manual sorting and subtotalling is risky because it changes row order, can split merged cells or formulas, and is easy to mistype. If Ritu later adds a row, the manual subtotal will not update. If she still wants subtotals, she should work on a copy, use Data > Subtotal, and cross-check the grand total. The category summary should be checked against the SUM of column H so that no row is counted twice or missed.