COUNTIF With Multiple Criteria In Google Sheets

What Is COUNTIF With Multiple Criteria In Google Sheets?

COUNTIF function in Google Sheets, as the name suggests, is a function used to find the total count with a condition or criteria. It is a default function available in both Excel and Google sheets.

COUNTIF Google sheets function is similar to the COUNT function in Google sheets. It helps people count the values from a data based on a condition. For example, consider the below table showing values in column A.

Now, let us count the number of times the value ‘10’ is available in the cell range.

To begin with, select the cell A8 and insert the COUNTIF function in Google sheets formula, =COUNTIF(A2:A7,10). Press Enter key.

We will be able to see the result in cell A8, as shown in the below image.

Likewise, we will be able to count the values in the data range using COUNTIF function in Google sheets with detailed examples.

COUNTIF With Multiple Criteria() Google Sheets Formula

The formula or syntax of COUNTIF with multiple criteria is =COUNTIF(range,criterion)

where

• Range is the cell range used to count the certain value
• Criterion is the condition or criteria used to find and count in the cell range

Both the arguments are mandatory in this function.

How To Use COUNTIF With Multiple Criteria In Google Sheets?

We can use the COUNTIF with multiple criteria in Google sheets with one of the two methods. They are:

• Select the COUNTIF function under the Insert tab
• Manually type the COUNTIF function

Method #1 – Select The COUNTIF Function Under The Insert Tab

The steps are:

Step 1: To begin with, we need to insert the data in the Google sheets. Next, select the cell where we want to find the result.

Step 2: Next, click on the Insert tab. Click on the Function group.

Step 3: Then, click on the Math function group. Click on the COUNTIF function formula in Google sheets.

Step 4: We will be able to see the COUNTIF function in the cell. Next, select the cell range and enter the condition or criteria.

Step 5: Press Enter key.

We will be able to see the result in the active cell.

Likewise, we can insert and use COUNTIF function in Google sheets from Insert tab.

Method #2 – Manually type the COUNTIF function

The steps are:

Step 1: To begin with, we need to insert the data in the Google sheets. Next, select the cell where we want to find the result.

Step 2: Next, enter the =COUNTIF or =COUNT. Then, click on the COUNTIF function.

Step 3: Then, select the cell range. Enter the condition or criteria.

Step 4: Press Enter key.

We will be able to see the result in the active cell.

Examples

Likewise, we can insert and use COUNTIF function in Google sheets manually.

Now, let us learn how to use COUNTIF function with multiple criteria in Google sheets with the following examples.

Example #1 – COUNTIFS With Multiple Criteria In The Same Range

For example, consider the below table showing region and values in column A and B, respectively.

Now, let us learn how to count the number of times the value ‘7’ is available in the cell range using COUNTIF Google sheets function.

The steps are:

Step 1: To begin with, we need to insert the data in the Google sheets. In this example, the data is available in the cell range A1:B8. Next, select the cell where we want to find the result. In this example, we need to select the cell B8.

Step 2: Next, insert the COUNTIF formula in Google sheets.

Step 3: Then, select the cell range and condition (criteria).

So, the complete formula is =COUNTIF(B2:B7,7)

Step 4: Press Enter key.

We will be able to see the result in the cell B8 in the below image, showing the number of times the value is included in the data.

Likewise, we can use COUNTIF function in Google sheets with single criteria.

Example #2 – Using SUMPRODUCT For Multiple Criteria

For example, consider the below table showing product and price in column A and B, respectively.

Now, let us learn how to count the number of times the value ‘Apple’ and ‘10’ are available in the cell range using COUNTIF Google sheets function.

The steps are:

Step 1: To begin with, we need to insert the data in the Google sheets. In this example, the data is available in the cell range A1:B8. Next, select the cell where we want to find the result. In this example, we need to select the cell B8.

Step 2: Next, insert the formula along with the COUNTIF formula in Google sheets and select the cell range and condition.

So, the complete formula is =SUMPRODUCT(COUNTIF(A1:A7,”Apple”),COUNTIF(B1:B7,”10″))

Step 3: Press Enter key.

We will be able to see the result in the cell B8, showing the number of times the value is included in the data using multiple criteria (i.e., by using SUMPRODUCT and COUNTIF function formula)

Likewise, we can use COUNTIF function in Google sheets with multiple criteria.

Example #3 – Using COUNTIF With An Array Formula

For example, consider the below table showing test 1, test 2 and test 3 values in columns A, B and C, respectively.

Now, let us learn how to count the number of times the value ‘50’ is available in the cell range A1:C7 using COUNTIF Google sheets function with an Array formula.

The steps are:

Step 1: To begin with, we need to insert the data in the Google sheets. In this example, the data is available in the cell range A1:C7. Next, select the cell where we want to find the result. In this example, we need to select the cell F1.

Step 2: Next, insert the COUNTIF formula in Google sheets with array formula and select the cell range and condition.

So, the complete formula is =SUM(COUNTIF({A1:A7,B1:B7,C1:C7},50))

Step 3: Press Enter key.

We will be able to see the result in the cell F1, showing the number of times the value ‘50’ is included in the cell range A1:C7.

Likewise, we can use COUNTIF function in Google sheets with an array formula.

Important Things To Note

• COUNTIF function is a default function used to count the number of times a values is seen in the data with a condition.
• Remember, the COUNTIF function reads both numeric and non-numeric values such as text, symbols or punctuation marks.
• We can combine the COUNTIF function with SUMPRODUCT and array formula in Google sheets.

Frequently Asked Questions (FAQs)

Recommended Articles

Guide to What Is COUNTIF With Multiple Criteria In Google Sheets. We learn how to use it to count values with various criteria using examples. You can learn more from the following articles. –

Subtract In Google Sheets

Daverage Google Sheets

Add Column In Google Sheets