Colquhoun.AI
← All guides

Research Guides

Keep Total Rows Out of Your Research Sum

· Colquhoun.shop editorial

A research spreadsheet can contain correct numbers and still produce an incorrect total. The risk is especially easy to miss when an export combines individual categories with subtotals and an overall summary. Before adding a column, decide what each row represents.

This is a manual review method for data you are authorized to analyze. It does not describe an automatic Colquhoun.shop validation feature or certify the accuracy of an external export.

1. Write down the level of each row

Record the metric, reporting period, filters and row definition before calculating. Add a working classification such as detail, subtotal or overall total. Keep the original file untouched and make these annotations in a review copy.

The European data portal's visualisation guide warns that aggregate values mixed with underlying rows can lead to double counting. It also advises investigating repeated identifiers or identical rows rather than proceeding without understanding them. Data.europa.eu: duplicates, aggregates and totals

2. Try a small arithmetic check

Imagine an invented website report with three mutually exclusive landing-page groups for the same period: guides have 120 sessions, product pages have 80 and other pages have 50. The export also includes an overall total of 250. Summing all four rows gives 500, even though the underlying report still describes only 250 sessions.

Use the three detail rows to calculate 250, then compare that result with the supplied total. Keep the total as a reconciliation check, not as a fourth category. If the result differs, investigate filters, missing groups, rounding and definitions instead of forcing the figures to match.

3. Do not mistake different labels for separate populations

Removing a row named “Total” is not enough. A report could show mobile sessions and paid-search sessions, with some sessions belonging to both. Those labels describe different dimensions, so their counts are not automatically additive.

Ask whether every counted item belongs to exactly one included group under the source's definitions. If that cannot be established, show the groups separately and explain the overlap. Do not invent a combined audience or subtract an assumed overlap just to produce one headline number.

4. Investigate repeated rows before deleting them

Two rows with the same visible label may refer to different properties, dates or filters. Conversely, the same export may have been imported twice. Compare the full identifying fields and provenance before deciding that a row is a duplicate.

Keep an exclusion log with the original row reference and reason. “Excluded supplied overall total from detail sum” is reproducible. “Cleaned the spreadsheet” does not tell the next reviewer which evidence was removed.

5. Attach a reconciliation note

Publish the result only at the level the evidence supports. If the source gives several overlapping views, an honest set of separate observations is more useful than one inflated total. A calculation can reconcile perfectly and still use the wrong scope, so retain both the arithmetic and the definition of what was counted.

This guide describes the built-in Web Reader and Keyword Density behavior verified for this publication. Available data depends on the page response; these tools do not measure visits or search rankings.