How to Flag Incomplete Records in Excel

Count missing fields per row with COUNTBLANK, block records missing critical data with OR, and total the problems.

You will learn to

• Count the empty fields in a record
• Separate blocking gaps from minor ones
• Count records missing any critical field

How it works

COUNTBLANK(range) counts the empty cells in a range, so =COUNTBLANK(A3:E3) tells you how many fields a record is missing. Wrap it in IF to label rows Complete or Incomplete.

Not every gap matters equally. Use OR for the critical fields — =IF(OR(B4="",D4=""),"Blocked", …) — and COUNTBLANK for the rest. To count how many records have any critical gap, add the blank tests together inside SUMPRODUCT and check whether the sum is above zero, which is OR logic across a whole column.

=IF(OR(B4="",D4=""),"Blocked",IF(COUNTBLANK(A4:E4)>0,"Incomplete","Complete"))