COUNTIF Multiple Criteria in Google Sheets (3 Ways)

A veteran in Excel will tell you how powerful the COUNTIF function and all its iterations are. The same importance is carried over to Google Sheets.

In this article, we will be looking at how we can count with multiple criteria with COUNTIF, its close iterations, and a couple of alternatives, in Google Sheets.

Let’s get started.

COUNTIF for Multiple Criteria in Google Sheets- AND Logic

1. Using COUNTIFS for Multiple Criteria

The simplest way to count for criteria in Google Sheets is to utilize the COUNTIFS function. This function was literally created for such situations.

The COUNTIFS function syntax:

COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...])

COUNTIFS syntax for countif multiple criteria in google sheets

  • criteria_range1: The range of cells that we will check
  • criterion1: The value that we will check the range with
  • [criteria_range2, criterion2]: Optional. The second section of the range and criterion.

More criteria can be added accordingly.

To showoff this function, we have a simple dataset on which we can perform counts based on multiple conditions:

base dataset for countif multiple criteria in google sheets

For our first count, our conditions will be: More than 100 units sold in the northern region.

  1. Quantity Sold: >100
  2. Region: North

Let’s put these conditions into action.

Step 1: Select the cell where you want your count to be and type =COUNTIFS(

applying COUNTIFS to the table

Step 2: Add the two conditions with their respective ranges:

Criteria 1: E3:E12,”>”&100

Criteria 2: C3:C12,”North”

Our final formula:


highlighting the two criteria in COUNTIFS

Step 3: Close parentheses and press ENTER

countif multiple criteria in google sheets result with COUNTIFS

We have successfully counted the number of records in our dataset that satisfies multiple criteria!

Note that the greater than symbol (>) is within quotation marks (“”) as it is seen as a text value, and it is concatenated with the numerical value, 100, with an ampersand (&).

We can avoid this by directly using cell references for our conditions:


cell reference for COUNTIFS

From this point forward, we will be utilizing cell references for our criteria.

2. Using COUNTUNIQUEIFS Function

The COUNTUNIQUEIFS function is a great spin on the base COUNTIFS function. It fundamentally works the same way, but it only considers unique records in the range designated by the user.


COUNTUNIQUEIFS(count_unique_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])

COUNTUNIQUEIFS syntax for countif multiple criteria in google sheets

  • count_unique_range: The range reference from which the unique values will be counted.
  • criteria_range1: The range of cells that we will check
  • criterion1: The value that we will check the range with
  • [criteria_range2, criterion2]: Optional. The second section is of range and criterion.

The count_unique_range virtually acts as a criterion on its own.

For our test, we will be counting all the unique regions that have less than 100 products sold and have an earning lower than $5000.

  • Unique range: C3:C12
  • Criteria 1: E3:E12,C15
  • Criteria 2: F3:F12,C16

Final Formula:


According to COUNTIFS criteria, the following records satisfies the conditions:

COUNTIFS count results

But COUNTUNIQUEIFS will take these 3 unique values:

COUNTUNIQUEIFS count results


COUNTIF for Multiple Criteria in Google Sheets – OR Logic

By default, the COUNTIF function only works with one range and one criterion. However, you can add or subtract or combine other functions with it to give an overall count. But this is only for a single type of data or column of data.

So, it is highly unlikely you will need to make it so complex when you have a function like COUNTIFS available. But we are going to see how we can achieve this anyways.

1. Adding Counts: COUNTIF + COUNTIF

For our example, we will be counting the number of records of regions East and West.

The formula is simple, we will add two instances of the COUNTIF function:


countif multiple criteria in google sheets OR type addition

We have two East records and two West records:

results of OR type COUNTIF addition

2. Subtracting Counts: COUNTIF – COUNTIF

Now, we are going to find the number of records between a range of numbers, namely, the records that have a Products Sold value between 100 and 300.

Since we cannot put both number conditions in a single COUNTIF, we will be subtracting the count of one range from the other:


countif multiple criteria in google sheets OR type subtraction

Records under 300: COUNTIF(E3:E12,C15) = 9

Records under 100: COUNTIF(E3:E12,C16) = 6

9 – 6 = 3


3. Single Column, Multiple Criteria with COUNTIFS

If we want to use multiple criteria in the same column with COUNTIFS, we have to make use of the OR logic and array values. Array values are denoted to be enclosed in curly braces {}.

Our idea is simple for our example. Like we have seen previously, we will be summing all the instances that have a region value of East and West.

A point to note before we dive in, Google Sheets works with arrays differently compared to Excel. For Sheets, we have to use the function ARRAYFORMULA when we are working with a formula that has array values.

That said, our formula this time will be:


counting for multiple criteria in a single column with ARRAYFORMULA

You can replace cell references C15 and C16 with “East” and “West” respectively.



QUERY is a pretty powerful function in Google Sheets. If you can work with it, you can replicate virtually any other formula, even COUNTIFS. This makes QUERY the perfect alternative function to COUNTIF for multiple criteria in Google Sheets.

To show it off, let’s bring down the COUNTIFS formula that we have used before:


Its equivalent QUERY formula will be:

=QUERY(B3:F12,"select count (C) where C='North' and E>100",0)

using QUERY as an alternative to count for multiple criteria

Formula Breakdown:

  • B3:F12: Our data range
  • select count (C): We are counting for the data in column C
  • where C=’North’ and E>100: Our two criteria
  • 0: No headers were selected

Note: “count” is an automatic header of the query.


The other alternative we are going to be discussing today is the SUMPRODUCT function.

We will again apply the COUNTIFS equivalent formula (=COUNTIFS(E3:E12,">"&100,C3:C12,"North") in a SUMPRODUCT version:


using SUMPRODUCT to count for multiple criteria

Final Words

We hope that we were able to make to grasp the fundamentals and the different processes through which you can COUNTIF for multiple criteria in Google Sheets. Hopefully, you can now use them with ease in your spreadsheets.

Feel free to leave a comment with any queries or advice you might have.

Related Articles for Reading

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply