Why Is My Open Pipeline Showing Zero?

A confident zero from a report with plenty of open deals behind it. The date field is nearly always the cause, the check takes one formula, and the fix is in your CRM rather than your spreadsheet.

By Ed · Published August 20, 2026 · 6 min read

Twenty-five years starting companies, consulting, owning a multi-million-dollar P&L and leading business intelligence for large enterprises.

A pipeline report returns 0.00. There are ninety open deals in the CRM and several million in play. Nothing has broken, nothing is corrupted, and the report is doing exactly what it was asked. This is one of the most common failures in CRM reporting and it has a one-formula diagnosis.

The cause, nearly every time

Open pipeline is a windowed figure: open deals expected to close in a period. The window is applied to the expected close date, and that field is optional in every CRM.

A deal with a blank close date cannot satisfy a date comparison. Not because it is excluded by a rule, but because a comparison against a blank is never true — the deal falls outside every window at every width, and widening the range does nothing at all.

Which produces the specific and confusing symptom: the report is not wrong about the deals it found, it found none, and it reported the sum of nothing as zero.

The check

Open deals            =COUNTIFS(Deals[Stage],"<>Closed Won",
                                Deals[Stage],"<>Closed Lost")
Open, no close date   =COUNTIFS(Deals[Stage],"<>Closed Won",
                                Deals[Stage],"<>Closed Lost",
                                Deals[Close date],"")

If the second number equals the first, you have your answer and the date range is irrelevant. If it is a large fraction, your figure is not zero but it is understated by however much those deals are worth — which is the more insidious version, because a number that is merely too small looks like a number.

Add the value while you are there:

Value at risk  =SUMIFS(Deals[Amount],Deals[Stage],"<>Closed Won",
                       Deals[Stage],"<>Closed Lost",Deals[Close date],"")

Two other causes, less common

Stage labels that do not match. A filter excluding Closed Won against an export containing closedwon excludes nothing, so the figure includes every closed deal and is far too large rather than too small. The mirror version — including a stage label nothing matches — returns zero. Total your stage counts and check they sum to the export's row count.

Amounts imported as text. A column of numbers stored as text sums to zero and looks completely normal. =COUNT(Deals[Amount]) against =COUNTA(Deals[Amount]) tells you immediately: if the first is much smaller, they are text.

Fixing it properly

The temptation is to default the missing dates in the report — to today, or to the end of the quarter. Do not. That puts real money into a period on the strength of a guess, and the guess disappears the moment it is applied. Whoever reads the figure next has no way to know a third of it was invented.

The fix belongs in the CRM. An expected close date on an open deal is a real piece of sales information, not a reporting formality, and a deal nobody will commit to a date for is itself a finding. Where the gap cannot be closed quickly, the honest reporting treatment is to show those deals as their own bucket — open, no expected close date — with their value visible, so the figure is complete and the gap is legible.

From shipping this exact defect

We printed 0.00 for open pipeline for four days in our own demo, under a badge that said the answer came from a governed definition. Every deal in the sample was open, none carried a close date, and the window therefore matched nothing. The arithmetic was flawless and the answer was useless.

What made it bad was not the zero. It was the confidence: a total over zero rows is zero, and nothing in the output distinguished that from a genuine, informative zero. A report should say when its window matched nothing, and now ours does. It cost four days to learn that a confident wrong answer is worse than a visible failure.

What a tool should do about it

This generalises past pipeline. Any windowed aggregate over an empty set produces zero, and zero is indistinguishable from a real result. That applies to revenue in a month with no orders, spend on a campaign with no rows, and every metric that takes a date range.

The behaviour worth demanding is that the tool tells you the window was empty rather than answering as if it were not. It is a small thing to build and it is the difference between a number you can act on and one you cannot.

What we do now

Quiriz for CRM computes open pipeline as the amount of deals that are neither won nor lost, windowed on the expected close date, and broken down by rep, team, region, stage, source or deal type. In Excel that is =QUIRIZ.ASK("open pipeline by stage", "table").

Where the window matches no rows, the answer says so instead of reporting a confident 0.00. Deals whose stage is not recorded are bucketed visibly rather than dropped, for the same reason.

The honest boundary: we cannot supply an expected close date that was never entered. Deals without one are outside any dated question, and the useful thing a tool can do is make that visible rather than silently absorb it. The related trap on the same data — reading a weighted figure as a prediction — is in weighted pipeline is not a forecast.

Before adjusting a date range, count your open deals with no close date. If that count is the whole pipeline, no date range will ever help.

Find out what your pipeline report is not counting

Upload your CRM export and ask for open pipeline by stage or by rep, and see what falls outside the window. Free to start.

Try Quiriz free →

Frequently asked questions

Why does my pipeline report show zero when I have open deals?

Almost always the date window. Open pipeline is filtered to a period using the expected close date, and a deal with that field blank cannot satisfy any date comparison — so it falls out of every window at every width. A team that does not maintain expected close dates on open deals can have a full pipeline and a report that says zero.

How do I check whether close dates are missing?

One formula: count open deals where the expected close date is blank, and compare it to the count of open deals overall. If the two are equal, that is your answer, and no amount of adjusting the date range will change it.

Should a missing close date default to something?

Not silently. Defaulting to today, or to the end of the quarter, puts real money in a period on the strength of a guess, and the guess becomes invisible the moment it is applied. Either exclude those deals and say how many were excluded, or report them in their own bucket. Both are honest; a silent default is not.

What should a reporting tool do when the window is empty?

Say so. A total over zero rows is arithmetically zero, and printing 0.00 is a confident answer to a question that could not be answered. The useful behaviour is to report that the window matched nothing and why — which is what distinguishes a wrong answer from a useless one.

Could something other than the date cause this?

Yes, two things. A stage filter written against labels your export does not use — excluding "Closed Won" when the file says "closedwon" — will either exclude nothing or exclude everything. And an amount column that imported as text sums to zero while looking entirely normal on screen. Check the date first; it is the most common by a wide margin.