How to use COUNTIFS in Google Sheets

Get a selection of expert articles

COUNTIFS is one of the many Google Sheets count functions. This formula works as its name suggests: it counts values within a range that match all established conditions.

As we will see, this function is quite simple yet useful for a variety of applications.

Spreadsheets are just the start

Syntax

The structure of this function consists of one or more range/condition pairs. If 3 such pairs are established, the function will check the ranges and count it if all conditions are true.

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

  • criteria_range1 – the first criteria range to evaluate.
  • criteria1 – the condition to evaluate against the range.
  • Just repeat this for every condition you want to match.

Evaluate numbers

To evaluate numbers criterias can include any of the comparison operators in the table below.

> Greater than
>= Greater than or equal to
= Equal to
<> Not equal to
< Less than
<= Less than or equal to

So, for example, you could check that values are equal to “1” in column A and more than “20” in column B.

=COUNTIFS(A:A, “=1”, B:B, “>20”)

Evaluate text

To evaluate text you don’t need to include any operators, simply include the text in between quotations. The function will look for exact matches.

=COUNTIFS(C:C, “John”)

You can even find partial matches with the * wildcard which will match anything. The example below will match any text starting with the word “New”.

=COUNTIFS(C:C, “John”, D:D, “New*”)

Match blank cells

To match empty cells all you have to do is include two quotations with nothing in between.

=COUNTIFS(C:C, “John”, D:D, “New*”, E:E, “”)

COUNTIFS not blank

To count cells that aren’t blank, just include the not equal to operator between quotations.

=COUNTIFS(C:C, “John”, D:D, “New*”, E:E, “<>”)

Add boolean expressions

You can even input Boolean values (TRUE and FALSE) as conditions. Keep in mind, avoid using quotations so Google Sheets recognizes these as Booleans and not text.

This is particularly useful if you have checkboxes as they are just a visual representation of these values. If a checkbox is empty it’s FALSE and if’t filled it is TRUE.

=COUNTIFS(A:A, TRUE, B:B, FALSE)

COUNTIFS across columns

Usually you include columns as ranges, meaning Google Sheets will evaluate one row at a time. But you could turn this around and include rows in the ranges, in this case the COUNTIFS will evaluate the conditions one column at a time.

=COUNTIFS(1:1, “>20”, 2:2, TRUE)

COUNTIFS between two dates

Using dates to exclude values from the count is quite simple, just express them as you would any other value.

=COUNTIFS(C4:C12, “>01/01/2016”, C4:C12, “<01/01/2017”)

Sheetgo workflows

If you are looking for new and better ways to manage your data, Sheetgo workflows are a great solution. Connect your data sources and process your information, automate entire processes and create great-looking dashboards.

While COUNTIFS is a great way to handle data, Sheetgo lets you manipulate information across multiple spreadsheets. It even allows you to incorporate forms for accurate data entry.

Fine tune your data streams, get information delivered to all stakeholders and automate your work with Sheetgo.

COUNTIFS in Google Sheets

This function is a powerful tool to analyze your data based on multiple criteria. It can evaluate numbers, match text, or handle Boolean expressions. By mastering this function, you can significantly enhance your data processing capabilities, ensuring accurate and efficient results.

For even more advanced data management, consider integrating Sheetgo workflows to automate processes and create comprehensive dashboards.

Ready to streamline your spreadsheet data?

You may also like…