You set it up carefully. Every row makes sense, every column has a name, and it still won't give you a total or a summary without a fight. That is maddening, and it is not you. Nearly everyone who builds a tracking sheet hits this, because nobody tells you the one rule that makes them work. It isn't a formula problem. It's structure, and once you can see it, this is a fix, not a rebuild.
The one rule every tracking sheet needs
A spreadsheet can do two things: store information and calculate from it. Most tracking sheets quietly try to do both at once in the same space, and that's what breaks them.
The rule is: a tracking sheet is a flat table. One header row at the top. One row per thing you're tracking — one job, one invoice, one client. Every column holds one type of information — a date, a name, a status, a dollar amount. Nothing else inside the table: no merged cells, no summary rows between the data, no totals in the middle.
When a table follows this rule, you can filter, count and total from it in a few seconds. When it doesn't, almost nothing works.
Five things that stop a tracking sheet from reporting
If your sheet isn't giving you answers, look for these first.
Merged cells. A merged heading that spans several columns looks clean. It also breaks every filter and formula that tries to read below it. Unmerge them — use a consistent header in every column.
One cell holding multiple values. A cell that says "John, Sarah" or "Xero, MYOB" can't be filtered or counted reliably. If a row has two people assigned, use two rows — or add a separate column for each value.
Dates stored as text. A mix of "15/09/26," "Sep 15," and "15 September" in the same column looks fine to read. A spreadsheet can't sort or group text dates. Use a consistent date format and let the app treat the column as a date — that one change fixes sorting, grouping and age calculations automatically.
Summary rows inside the data. A row that says "TOTAL" or a coloured divider between months. These look organised. But a formula or pivot table trying to read the full range will include those rows as data and give you wrong answers. Move all summaries below the table, or onto a separate sheet.
Status values that don't match. A status column where the same idea is written differently — "done," "Done," "DONE," "complete," "finished." Filters and counts treat each as a separate value. Pick one word per status, use it consistently, and your counts will be right.
How to get answers from a flat table
Once the table is clean and consistent, there are two quick tools.
A filter shows only the rows you want: click the filter icon in the header, pick a column, and select what to show. To see only the open jobs in September, you filter on status and on month.
A pivot table answers "how many" and "how much" questions: insert one from the menu, drag the column you want to count into the rows area, drag the value column into the values area. It recalculates automatically every time you add a row.
If you're in Excel and your data lives across several files — one per month or one per project — Power Query can pull them all into one flat table without you copying and pasting. You clean the structure once, and it stays current.
What "working" looks like
A tracking sheet that works lets you answer a question in a few clicks: how many open jobs are there this month, what's outstanding from this client, which status has the most rows. If you're copying numbers into a summary by hand, the structure needs fixing — not more columns or more formulas.
The information you need is probably already in your sheet. This is a structure fix, not a rebuild.