Google Sheets Cheat Sheet

COUNTA Google Sheets Function [With Quiz]

In Google Sheets the COUNTA function COUNTs All values present in the arguments provided.

If you need to count only numerical values, use the COUNT function instead.

COUNTA Syntax

=COUNTA(value1, [value2, ...])
  • value1 = a value, cell reference, or range reference from which to count values.
  • [value2, ...] = [optional] additional values, cell references, or range references to count.

All data types are counted by COUNTA. The common ones AND the obscure ones:

  • Numbers (positive, negative, zero)
  • Text
  • Boolean
  • Dates, times, durations
  • Images in cells
  • Charts in cells (sparklines)
  • Hyperlinks
  • Errors
hand pointing emoji hand pointing emoji

FREE RESOURCE

Google Sheets Cheat Sheet

12 exclusive tips to make user-friendly sheets from today:

Google Sheets Cheat Sheet

You'll get updates from me with an easy-to-find "unsubscribe" link.

COUNTA Function Example

ABC
1DataFormulaOutput
210=COUNTA(A2:A)5
3TRUE
41 January
5Text
600:30

The output is 5 because ALL values are counted.

The arguments to COUNTA can be provided as values (numbers, text, or BOOLEAN), cell references, or range references:

=COUNTA(1,"Text",TRUE,A1,B1:B2)

The COUNTA function counts whitespace and empty strings as values.

This can cause you to question its output. Especially if you use formulas like:

=IF(A1=A2,A1,"")

If A1=A2 is FALSE the output is an empty string ("") which appears in Google Sheets as a blank cell. However, COUNTA will still count it as a value.

I've encountered this quirk before and it can make outputs from formulas that rely on COUNTA very confusing.

COUNTA Errors

The COUNTA function outputs almost no errors.

It outputs effectively even when inputs include blank cells (which count as 0) and errors (which each count as 1).

The only error I could cause with COUNTA was not including any arguments:

=COUNTA()

Which results in an #N/A error:

Wrong number of arguments to COUNTA. Expected at least 1 arguments, but got 0 arguments.

COUNTA Function Quiz

Here's a quick summary of how to use the quiz spreadsheet:

  1. Select the required cell while the formula bar is empty
  2. Type your answer in the formula bar
  3. You can click on the spreadsheet to add cell/range references

Complete all questions to solidify your understanding of the COUNTA function.

COUNTA Function Exercises
Enter a formula for each question in the formula bar for the spreadsheet below.
1. Sales Team
In cell C2, enter a formula that counts how many salespeople there are in column A using a single range reference. Remember to start your formula with '='.
2. All Values
In cell C5, enter a formula that counts how many values are present in columns A and B using a single range reference.
ABC
1SalespersonAmountSales team
2Dwight500
3Jim425
4Stanley475Values
5Phyllis450
6Andy
7
Click Here When Complete

hand pointing emoji hand pointing emoji

FREE RESOURCE

Google Sheets Cheat Sheet

12 exclusive tips to make user-friendly sheets from today:

Google Sheets Cheat Sheet

You'll get updates from me with an easy-to-find "unsubscribe" link.

Kieran Dixon started using spreadsheets in 2010. He leveled-up his skills working for banks and running his own business. Now he makes Google Sheets and Apps Script more approachable for anyone looking to streamline their business and life.

Want Better-Looking Google Sheets?

Google Sheets Cheat Sheet

Get my 12-tip cheat sheet that will make your spreadsheets more user-friendly.

You'll get updates from me with an easy-to-find "unsubscribe" link.

🗙