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.
How to use COUNTIFS in Google Sheets
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 connections
If you are looking for new and better ways to manage your data, Sheetgo is a great solution. Connect your data sources and process your information.
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.
