KPI Summary Tiles with COUNTIF

Excel Formulas › Dashboards & Reporting

All versionsCOUNTIF

Build dashboard count tiles — Open, In Progress, Done — straight from a data table. COUNTIF tallies each status, and percentages turn raw counts into an at-a-glance status board.

Quick formula: count rows in a status:
=COUNTIF(status_column, "Open")
Count how many rows have each status; one COUNTIF per tile, plus a total and percentages.

Functions used (tap for the full reference guide):

COUNTIFCOUNTIFS

The example

42 Open, 88 In Progress, 120 Done.

AB
1TileCount
2Open42
3Done120

The formula

The formula:

=COUNTIF(status_column, "Open") // one COUNTIF per tile

How it works

How it works:

  1. One COUNTIF(status, "Open") per tile — Open, In Progress, Done, Blocked.
  2. A total tile is COUNTA of the status column (or SUM of the tiles).
  3. Show percentages: count / total — the share in each state.
  4. Slice by owner or date with COUNTIFS for filtered tiles.

Tiles + conditional formatting = a status board. Lay the COUNTIF tiles in a row, format each as a big number with a colored fill, and you have a live summary that updates the instant the underlying table changes. Add a stacked bar driven by the same counts for the mix, and the dashboard tells the whole story without a PivotTable.

Try it: interactive demo

Live demo

Counts per status.

Total · Done

Variations

Total tile

All rows:

=COUNTA(status_column) - 1

Percent in status

Share:

=COUNTIF(status, "Done") / total

Filtered tile

By owner:

=COUNTIFS(status, "Open", owner, "Sam")

Pitfalls & errors

Consistent labels. Status text must match exactly for COUNTIF.

Total = sum of states. Reconcile tiles to the row count to catch unlabeled rows.

COUNTIFS to filter. Add criteria for owner-, date-, or team-specific tiles.

Practice workbook

📊
Download the free KPI Summary Tiles with COUNTIF practice workbook
A summary-tiles sheet with the total, percent, and filtered variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I build KPI count tiles in Excel?
One COUNTIF per status: =COUNTIF(status_column, "Open"), plus a total and percentages.
How do I show the percentage in each status?
Divide by the total: =COUNTIF(status, "Done") / total.
How do I make a tile for one owner?
Use COUNTIFS: =COUNTIFS(status, "Open", owner, "Sam").

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Count if contains · COUNTIFS multiple criteria · RAG status

Function references: COUNTIFCOUNTIFS