COUNTA
Counts the cells in a column that are not blank, whatever type they hold.
COUNTA(<column>)Returns: A whole number (a scalar). Zero when every cell is blank.
What it does
COUNTA counts the cells in a column that are not blank. It works on numbers, text, dates and true or false values alike. Use it for completeness tests on any column, such as how many customers have a name or a tax ID on file. It counts each cell once, so duplicate values are each counted.
Syntax and arguments
- column: a column reference such as
Customer[CustomerName]. Only one column is accepted.
Example
The AR team wants to know how many customers in the list have a credit contact on file, which is a prerequisite before sending dunning letters.
Customers With Contact = COUNTA(Customer[CustomerName])
If CustomerName is filled in for every customer, this counts every customer. Comparing it with COUNTROWS(Customer) reveals the gap. Any difference means rows exist without a name.
Example: a second, different use
A control check for the sales data, counting the lines that carry a cost, without caring what the value is:
Lines With Cost = COUNTA(Sales[TotalCost])
A zero cost counts as a value, because it is not blank. If you need to exclude zeros, use COUNTROWS with a filter instead.
Things to watch
- Empty text from some sources can be counted as a value. Check the data in the model if the count looks too high.
- Zeros and any other value count. A number of zero is still a filled cell.
- Duplicates are counted every time. For distinct values, use DISTINCTCOUNT.
- COUNTA counts cells in a column. It is not a row count. A table with 100 rows and a name column with 98 names gives 98.
- COUNT is the number-only version. Pick COUNT for amounts and COUNTA for mixed content.
In business terms
COUNTA is the quick completeness check of close and audit work: how many vendors have a tax form on file, how many accounts have an owner assigned, how many invoices have a PO number. Gaps found this way are often the reason a report does not tie out.