Logical & Lookup Formulas
Validate Required Row Inputs with Check Mark & Type Guard (COUNTA & ISNUMBER)
Display an instant Unicode check mark ("✓ Ready") only when all required row fields are populated and numeric amounts pass ISNUMBER—preventing Excel’s silent "text > number" comparison trap.
Why naive >= 0 checks pass text strings like "TBD" or "$150"!
Intake templates for vendor onboarding and expense claims look far cleaner when column F shows a crisp "✓ Ready" check mark as soon as all 4 mandatory fields (Vendor, Category, Invoice #, Amount) are valid—and a specific missing-field count otherwise! Counting populated cells with COUNTA(B2:E2) = 4 catches empty cells, while ISNUMBER(E2) catches a notorious spreadsheet trap: in Excel’s type-sort hierarchy, ANY text string (even "0" or "N/A") evaluates greater than EVERY number ("N/A" > 1000000 is TRUE)!
Combining =IF(COUNTA(B2:E2) < 4, "✗ Missing " & (4 - COUNTA(B2:E2)), IF(NOT(ISNUMBER(E2)), "✗ Non-Numeric Amount", IF(E2 <= 0, "✗ Amount <= 0", "✓ Ready"))) guarantees that both completeness AND numeric type safety are verified before a row is approved!
| Row Submission State (4 Required Fields) | COUNTA(B2:E2) | ISNUMBER(E2) Check | Status Badge Returned |
|---|---|---|---|
| All 4 Fields Filled, E2 = 1250.00 | 4 | TRUE (> 0) | "✓ Ready" |
| Invoice # Left Blank (3 of 4 Filled) | 3 | TRUE | "✗ Missing 1" |
| Amount Typed as Text "TBD" (4 Filled) | 4 | FALSE (Text > Number Trap!) | "✗ Non-Numeric Amount" |
Row-Level Completeness & Numeric Type Check-Mark Validator
Verifies 4 required entries via COUNTA(B2:E2) and enforces ISNUMBER(E2) and E2 > 0 before emitting "✓ Ready".
=IF(COUNTA(B2:E2) < 4, "✗ Missing " & (4 - COUNTA(B2:E2)), IF(NOT(ISNUMBER(E2)), "✗ Non-Numeric Amount", IF(E2 <= 0, "✗ Amount <= 0", "✓ Ready")))Batch Readiness Percentage Across All Intake Rows
Counts how many status badges in F2:F5 equal "✓ Ready" as a percentage of total rows.
=ROUND(100 * COUNTIF(F2:F5, "✓ Ready") / ROWS(F2:F5), 1)Try the example
Use the original values or change them to test your understanding.