How to Count Data Matching Set Criteria in Google Sheets

Situatie

The COUNTIF function in Google Sheets lets you analyze data in your spreadsheet and returns the number of times it appears in your document if it meets a set of specific criteria. Here’s how to use it.

Solutie

Use COUNTIF to Match on One Type of Criteria

Fire up Google Sheets and open a spreadsheet with data you want to count.

Click on an empty cell and type =COUNTIF(<range>,<criterion>) into the cell or the formula entry field, replacing <range> and <criterion> with the range of data to count and the pattern to test, respectively. It should look something like this:

=COUNTIF(F2:I11,"<=40")

After you press the “Enter” key, the cell will contain the number of times the range matches the exact criteria specified.

After you hit the Enter key, the amount of times the function matched will show in the cell.

If the range you want to count contains a string of text in it, it would look something like this:

=COUNTIF(C2:C11,"George")

However, that function would return zero results as none of the cells in the range contain the exact string “George.”

However, unless you use wildcards, the string needs to match exactly.

Instead, you need to use a wildcard to match all instances of directors with the first name George. You can use wildcards, such as “?” and “*” to match a single character and zero or more contiguous characters, respectively.

To search for every director with the first name “George,” you should use the “*” wildcard in the function. It would look like this:

=COUNTIF(C2:C11,"George*")

If the string you’re trying to match contains an actual question mark or asterisk, you can prefix it with the tilde (~) as an escape character (ie ~? and ~*).

Use COUNTIFS to Match Multiple Criteria

COUNTIF is excellent if you only need to match depending on one type of criteria; if you have more than one, you will use the COUNTIFS function. It works exactly the same as COUNTIF, except it accepts more than one range and/or criterion. Any additional ranges must have the same number of rows and columns as the first range.

Simply add as many ranges and criteria you want to count into the function. The format is basically the same as COUNTIF. It should look something like this:

=COUNTIFS(C3:C11,"George*",D3:D11,">=1990")

Whether you want to count a single set of criteria in a range or multiple ones, the use of both COUNTIF and COUNTIFS will help you analyze the data inside your spreadsheet.

Tip solutie

Permanent

Follow Us