Statistical
Count non-blank and missing cells by category (COUNTIFS data completeness %)
Audit CRM or vendor master-data completeness per region using COUNTIFS with "<>" (populated) and "" (blank) criteria.
Measure field presence by category
Data-governance and AP teams constantly need to answer: "What percentage of EMEA vendors have a populated Tax ID on file—and how many are still missing?"
Inside COUNTIFS, the criteria string "<>" counts non-blank cells (whether text or numeric!), while "" counts blank cells. Dividing Populated by Total Regional Rows COUNTIF(A2:A7, E2) delivers an instant Data Completeness Percentage!
Presence is not validity: a space, zero or arbitrary text can count as populated. These fictional TaxID strings are not validated identifiers. Empty formula results count as missing under this example. Category matching is case-insensitive and supports wildcard criteria; use ordinary category labels here.
| Region | Total Vendors | Tax ID Populated ("<>") | Tax ID Missing ("") & Completeness % |
|---|---|---|---|
| EMEA | 4 Vendors | 3 Populated | 1 Missing -> 75.0% Complete |
| AMER | 2 Vendors | 1 Populated | 1 Missing -> 50.0% Complete |
Count populated (non-blank) records for a specific category
Counts rows where Region A2:A7 equals E2 ("EMEA") AND TaxID B2:B7 is non-blank ("<>").
=COUNTIFS(A2:A7, E2, B2:B7, "<>")Calculate regional field completeness percentage
Divides populated count by total rows in that region; COUNTIFS(A2:A7, E2, B2:B7, "") counts missing records.
=IF(COUNTBLANK(E2)>0,"Choose category",IF(COUNTIF(A2:A7,E2)=0,"No records",ROUND(COUNTIFS(A2:A7,E2,B2:B7,"<>")/COUNTIF(A2:A7,E2),4)))Try the example
Use the original values or change them to test your understanding.