How to Use All Google Sheets Count Functions [All 8 Count Functions]

0
223
All Google Sheets Count Functions

Do you know how many count functions are there in Google Sheets? May be not, right? There are eight count functions in Google Sheets. The following are the all eight count functions in Google Sheets. 1. COUNT, 2. COUNTA, 3. COUNTUNIQUE, 4. COUNTBLANK, 5. COUNTIF, 6. COUNTIFS, 7. DCOUNT and 8. DCOUTA. Now let us learn how to use all Google Sheets count functions with proper examples.

Count:

How to Use Google Sheets Count Function?

We are going to learn all Google Sheets count functions and that begin with the COUNT function, which is the simplest one among the eight.

Below is the syntax.

Syntax: COUNT(value1, [value2, …])

Count returns the number of numeric values in a range or multiple ranges. We can learn Count function with few examples.

Sample Data:

sample date to learn count function

Now few count function formulas so that you can learn the Count function easily.

=COUNT(G2:G7)

This formula will return the result 5. The range in the formula covers six cells. But in one cell there is text value. The Count formula only counts numeric values in a given range. Now to the second formula.

=count(E2:E7,G2:G7)

Here I’ve used multiple ranges, which are not adjoining columns, to count. Here the result will be 10. The formula here counts numeric values in two different column ranges.

Now to the third Count function example.

=count(E2:G7)

Here in this formula multiple columns are covered. But all the columns in the range are adjoining.

Hope you got the clear picture of using the count function in Google Sheets.

Counta:

How to Use Google Sheets Counta Function?

Below is the syntax of Counta function. Need not pay much attention to syntax. Just read and jump to examples straightaway.

Syntax: COUNTA(value1, [value2, …])

This function is similar to count. Here the only difference is instead of numeric value, Counta counts both numeric as well as text values in given ranges.

sample data to use counta function

Pay your attention to the below three Counta function examples.

=COUNTA(G2:G7)
=COUNTA(E2:E7,G2:G7)
=COUNTA(E2:G7)

There are three formulas here as Counta examples. The fist formula counts values in a single column range, no matter whether it’s text value or numeric value and return the number. Here the result will be 6 – five cells with numeric value and one cell with text string.

The second formula you can use when you want to count multiple columns which are not adjoining.

The third formula you can use in multiple adjoining columns. There is nothing more with Counta function.

Countunique:

How to Use Google Sheets Countunique Function?

See the syntax of Countunique function below.

Syntax: COUNTUNIQUE(value1, [value2, …])

This function does the job of Count as well as Counta functions. But there is one important difference.

As the function name denotes, Countunique only returns the number of unique values in the included range. That means if any value repeats more than one time, it only count ones.

sample data to learn countunique

Examples:

=COUNTUNIQUE(G2:G7)
=COUNTUNIQUE(E2:E7,G2:G7)
=COUNTUNIQUE(E2:G7)

The first Countunique formula will check a single column for unique values, no matter the values are text or numeric. As per our above sample data it will return the result 5 as there is only one value repeated twice, that is 110,000 and the formula will count it as one.

The second formula make the use of multiple columns when they are not adjoining.

The third formula with multiple adjoining column ranges. That’s all about Countunique.

We are inching close to leaning how to use all Google Sheets Count Functions. There are only few more functions.

Countblank:

How to Use Countblank Function in Google Sheets?

See the syntax of Countblank below.

Syntax: COUNTBLANK(range)

This Google Sheets function is to count blank cells in single or multiple column ranges. The Countblank formula can be used similar to Count, Counta or Countunique.

sample data to learn countblank

See few examples below.

=COUNTBLANK(G2:G7)
=COUNTBLANK(E2:E7,G2:G7)
=COUNTBLANK(E2:G7)

In the first Countblank formula checks a single range for blank or empty cells and return the result 1 as there is only one empty cell in our sample data for the given range.

The second formula you can use in multiple different column ranges and the third formula in multiple adjoining column ranges.

In your progress to learn all Google Sheets Count Functions, the following functions are very important.

Countif:

How to Use Countif Function in Google Sheets?

Compared to the above count functions Countif is little complex. See the syntax first.

Syntax: COUNTIF(range, criterion)

You can use Countif function for conditional count.

sample countif data

Let us go through few Countif examples. I don’t want to waste your time by explaining the syntax which is no use.

=COUNTIF(G2:G7,110000)

This Countif formula checks one single column range and count the cells if the cell value is 110,000.00. We can only check one criterion or condition in Countif function.

=countif(E2:G7,200)

The above Countif function is useful when you want to count for certain value in multiple adjoining columns ranges.

As already told above, only one criterion can be used in Countif Google Sheets function. But here is a “tweak” to use multiple Criterion in Countif.

=countif(B2:B7,”North”)+countif(E2:E7,100)

Here I’ve used two Countif formulas to use multiple criterion. This is never an equivalent to Countifs which I will explain after this. So you can understand the difference.

You should thoroughly know how to use criteria in Countif. Here is one small chart to solve your dilemma in using different criteria in Countif. This will answer your following questions related to Countif.

  • How to use date as criteria in Countif?
  • Use of comparison operators as criteria in Countif?
  • How to use wildcard characters with criteria in Countif?

See the chart to know how to use the above different criteria in Countif.

Note: In cell C6 please consider the same criteria as in cell B6 but with a preceding “=” sign without quotes.

chart to learn criteria use in countif

Below is one example for using date with comparison operator. Please refer the above chart also.

=COUNTIF(D2:D7,”>”&date(2017,8,3))

That’s all with Countif Google Sheets function.

Similar: Simplified the Use of SUMIF function in Google Sheets

Countifs:

How to Use Countifs Function in Google Sheets?

First go through the Countifs syntax. Just read and leave it. We can learn the same with the help of my examples which are following.

Syntax: COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, …])

Using Countifs you can check multiple ranges with multiple criterion. If all the conditions are matching, then the formula will count it.

sample data to learn countifs

See the formula.

=countifs(C2:C7,”TV 2202″,F2:F7,470)

This Countifs formula will first check for column range C2:C7 for “TV 2202” and match with the corresponding cell under column range for 470. If both are matching with the criteria given, it will be included in the count.

The above formula result will be 3 as there are only three rows matching the criteria, they are row 2, 3 and 6.

See another formula.

 =countifs(D2:D7,”>”&date(2017,8,1),D2:D7,”<“&date(2017,8,10))

This Countifs formula checks column range D2:D7 for date between 01/08/2017 and 10/08/2017 and count the matching numbers.

Use the below chart to learn the criteria use in Countifs. In cell C6 you can see criteria as >42950. Got confused right? Actually I entered there the same criteria in cell B6 but with preceding “=” sign without quotes. Then it got changed automatically because Google Sheets read it as a formula.

Now to the eighth formula in all Google Sheets count functions. This’s the last count formula which is a database function.

Similar: How to Use SUMIFS Function in Google Sheets

Dcount and Dcounta:

Learn How to Use Dcount and Dcounta Functions in Google Sheets?

  • How to use Dcount function in Google Sheets?
  • How to use Dcounta function in Google Sheets?

Syntax: DCOUNT(database, field, criteria)

Dcount is a database function. So the usage of Dcount is entirely different from all other count functions in Google Sheets.

There is a basic difference between Countifs and Dcount. In Countifs we check two or more ranges with criterion and if found any match, it got included in the count.

In DSUM it also checks multiple range with criterion. The difference we can specify which range to count based on the matching criteria. If the range we specify, it’s called “field” in the arguments as per the syntax, contain empty cells, it normally skips that. When you use Dcount function the count field should always be numeric or date value. If you want to count both text and numeric value, then use Dcounta.

Straightway to examples. Below is the sample data.

sample data to dcount and dcounta

See the formula and criteria use below.

dcount example

In Dcount the criteria part to be entered under proper column labels as above. The Dcount formula above will count field no. 7, i.e. amount column, if “sales_person” name is “Philip Nida”, “area” is “North”, date is “02/08/2017” and “qty_sold” is 150.

You should use the function Dcounta if the field to count is text string or contain both text and numeric value.

See another variations of criteria use in Dcount below.

dcount criteria example

You try the above criteria variation with the sample data and see yourself what is the output.

If you want to compare two dates to count, follow the below criteria format in Dcount.

date comparison in dcount

And see the formula below.

=DCOUNT(A1:G7,7,D11:E12)

Now to become an expert in Dcount and as well as Dcounta refer the below criteria chart.

dcount criteria chart

Similar: How to Use DSUM in Google Sheets – Explained With Pictures

That’s the end of the tutorial.

LEAVE A REPLY

Please enter your comment!
Please enter your name here