📌 Stage 6 · Invoicing & Dashboard · Sales Pipeline CRM · Pipeline Dashboard · 40–50 min

Counting and summing everything except Won and Lost

Learning goals

  • Count open deals safely with COUNTA minus two COUNTIFs
  • Sum open pipeline value and weighted forecast by subtraction
  • Explain why '<>Won' criteria alone would inflate every count

Concepts

Why not just 'not equal to Won'?

The obvious formula for open deals is COUNTIF(stage,"<>Won") - and it is a trap. The aggregation ranges run to row 1004 on purpose (so the workbook grows), which means almost a thousand EMPTY rows sit under the data. An empty cell is not equal to "Won", so Excel counts every one of them: a six-deal pipeline would report 990. The safe pattern subtracts the knowns from the total: count everything (COUNTA counts only non-blank), then subtract Won, then subtract Lost.

=COUNTA(Opportunities!$G$5:$G$1004)-COUNTIF(Opportunities!$G$5:$G$1004,"Won")-COUNTIF(Opportunities!$G$5:$G$1004,"Lost")

Open deals = all filled stage cells (12) minus Won (5) minus Lost (1) = 6. Blank rows contribute nothing to COUNTA, so the empty tail of the range is harmless.

💡 Tips:

  • The same trap catches SUMIF with '<>Won' criteria - blanks satisfy that comparison too. Subtraction patterns are the defensive habit.

Open value and forecast, the same way

Money follows the identical shape: total the Value column, subtract what Won consumed and Lost removed - $154,200 - 70,200 - 9,800 = $74,200 of open pipeline. The weighted forecast swaps in the Weighted Value column (column J, built row by row in Lesson 9): total $101,415 minus Won's $70,200 minus Lost's $0 = $31,215.

Notice the dashboard does no clever per-row filtering - it reuses columns the registers already computed. That layering is the whole architecture of this workbook: typed facts at the left, row-level formulas at the right, sheet-level subtraction at the top. Every number can be traced by opening one cell.

=SUM(Opportunities!$H$5:$H$1004)-SUMIF(Opportunities!$G$5:$G$1004,"Won",Opportunities!$H$5:$H$1004)-SUMIF(Opportunities!$G$5:$G$1004,"Lost",Opportunities!$H$5:$H$1004)

Open pipeline value: all values ($154,200) minus won deals' values ($70,200) minus lost deals' values ($9,800) = $74,200.00. The third argument of SUMIF is the sum range - criteria and amounts can live in different columns.

💡 Tips:

  • SUMIF's sum range comes LAST (SUMIF(range, criterion, sum_range)) while SUMIFS puts the sum range FIRST. The swap is deliberate in Excel's design - SUMIFS grew the discipline later.

Practice

Fixed case (Jan-Jun 2026, aging date Jun 30, 2026). Pale-green cells are formulas; white cells are typed entries. This lesson opens on Dashboard.

  1. Type the open-value formula — C6 is blank. Click it and type =SUM(Opportunities!$H$5:$H$1004)-SUMIF(Opportunities!$G$5:$G$1004,"Won",Opportunities!$H$5:$H$1004)-SUMIF(Opportunities!$G$5:$G$1004,"Lost",Opportunities!$H$5:$H$1004) then Enter. It returns $74,200.00.
  2. Check the neighbors — C5 (open deals, the COUNTA pattern) shows 6; C7 (weighted forecast, the same subtraction on column J) shows $31,215.00. Three cells, the whole pipeline picture.
  3. Verify the arithmetic by hand — From Lesson 7: 18500 + 9600 + 7300 + 11400 + 22000 + 5400 = 74,200. The dashboard's subtraction and your row-by-row addition agree.
  4. Stress-test the pattern — Flip to Opportunities and note that rows 17-1004 are empty - then back to Dashboard: still 6 deals, because COUNTA ignores the empty tail. The trap is closed.

Online exercise

The spreadsheet below is editable — follow the practice steps right on this page. Steps that need ribbon menus should be done in your own Excel with the downloadable practice file.

表格加载中…

Checklist

Work through each item; when every box passes, this lesson is done:

  • ☐ C6 returned $74,200.00 via the subtraction pattern
  • ☐ Confirmed C5 = 6 open deals and C7 = $31,215.00 weighted forecast
  • ☐ Can explain why COUNTIF(stage,"<>Won") alone would count the blank tail of the range