What Most People Miss About COUNTA in Excel

A 2023 workplace survey of 1,247 finance and ops professionals found that 58% miscounted active rows in reports using COUNTA — not because they typed the formula wrong, but because they didn’t know how Excel defines “non-blank.”

The Problem

You’re auditing a supplier onboarding sheet. Column A holds vendor names, B has contract dates, C shows status (“Active”, “Pending”, “On Hold”), and D contains notes. You need to know how many vendors are *actually entered*, not just how many rows exist.

You type =COUNTA(A2:A20) and get 19. But when you scroll down, only 12 vendors have real names — the rest look blank. What’s going on?

Row	Vendor Name (A)	Contract Date (B)	Status (C)	Notes (D)
2	Acme Corp	2024-02-10	Active	Approved
3	BrightLine Solutions	2024-03-15	Pending
4		2024-01-22	On Hold	Legal review
5	CloudVault Inc	2024-04-01	Active	Renewed
6
7	=TRIM(B2)&"_"&C2			(formula result is "")
8	Zephyr Logistics	#N/A	Active	Final sign-off
9		2024-05-12	Pending	Awaiting docs
10	=IF(E2="","",E2)			(formula returns "")
11	Solaris Group	2024-06-03	Active	Pre-approved

Here’s what’s really happening:

Symptom	Cause	Fix
COUNTA(A2:A11) returns 10	Cell A4 contains a space (not empty), A7 and A10 contain formulas returning "" (still counted), A9 has 4 leading spaces	Use =SUMPRODUCT(--(TRIM(A2:A11)<>\ Rachel Torres Rachel coaches teams on email management and digital communication best practices. She has trained over 5 Table of Contents Related Articles The Only Outlook Trick You Need for Making Groups of Email Recipients Stop Striking Through Cells Wrong — Try This Instead What Most People Miss About Adding iCal to Outlook Stop Calling It 'Microsoft Excel' — What It Really Is Stop Copy-Pasting Signatures — The Only Outlook Trick You Need for Email Signatures The Only Excel Trick You Need for Printing Address Labels What Most People Miss About Changing Your Password on Hotmail Outlook Stop Copying File Names Manually — The Only Excel Trick You Need for This Stop Creating Outlook.com Accounts — Do This Instead What Most People Miss About How to Code in Excel OfficeAlibaba Your trusted source for office productivity insights, tool tutorials, and workplace essentials. Helping professionals work smarter since 2020. Topics Office Software Office Supplies Email & Communication Workplace Productivity Resources Guides Library Free Tools Templates Webinars Company About Us Contact Write for Us Privacy Policy Terms of Use © 2026 Office Alibaba. All rights reserved. Built for professionals who work smarter.