Count if row meets multiple criteria

Count if row meets multiple criteria

To count rows in a range that meets multiple criteria, use a formula based on the ROWS and FILTER functions or apply the SUMPRODUCT function.

How to count if a row meets multiple criteria?

  1. Select cell G3.
  2. Type =ROWS(FILTER(B3:E15,(C3:C15=”West”)*(D3:D15=”Desk”)*(E3:E15)>10))
  3. Press Enter.
  4. The formula returns the count of rows in the dataset that satisfy these conditions.

Example

Here’s how you can write a formula in a dynamic and modern way. In the example, we have a dataset in range B3:E15 containing information on orders, including columns for region, item, and units.

The goal is to count the number of rows that meet three criteria: the region is “West,” the item is “Desk,” and the units are greater than 10.

The formula in cell G3 uses the FILTER function to extract rows matching all three criteria by combining logical tests. The ROWS function is then applied to count the rows in the filtered result, displaying the count in cell G3.

Formula:

=ROWS(FILTER(B3:E15,(C3:C15=”West”) * (D3:D15=”Desk”) * (E3:E15)>10))

Count if row meets multiple criteria

The formula returns 2 matching items.

Explanation

Take a closer look at the formula and evaluate it!

ROWS Function: ROWS counts the number of rows in the provided array or range. In this formula, the array is the output of the FILTER function. Thus, ROWS calculates how many rows the FILTER function returns.

FILTER(range, include): The range to filter (B3:E15 in this case, which includes all columns for each row). Include is a logical condition specifying which rows to include. Only rows where this condition is TRUE will be included. The include condition is: (C3:C15=”West”) * (D3:D15=”Desk”) * (E3:E15>10)

Evaluating the logical condition:

  • C3:C15=”West”: Compares each value in C3:C15 to “West”. Results in an array of TRUE (if the value is “West”) or FALSE (if it isn’t).
  • D3:D15=”Desk”: Compares each value in D3:D15 to “Desk”. Results in another array of TRUE or FALSE.
  • E3:E15>10: Checks if each value in E3:E15 is greater than 10 and produces another array of TRUE or FALSE.

Here is how it works:

Combining with “*” (logical AND): The multiplication operator (*) performs a row-by-row multiplication on the three arrays. TRUE is treated as 1, and FALSE as 0. The multiplication ensures a row is included only if all three conditions are TRUE. The final result is: {0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 1}

Evaluating the logical condition - three criteria

The formula returns 2, since we have two rows that meet all three criteria:

  • Row 9: {4/7/2025, West, Desk, 27}
  • Row 15: {2/22/2025, West, Desk, 99}

Workaround with SUMPRODUCT

You can also use the SUMPRODUCT function to count rows meets multiple criteria. We use this formula to count how many rows meet all of the conditions in the dataset. For the sake of simplicity we created three named ranges: region, item, and units.

In this case, use the following formula:

=SUMPRODUCT((region=”West”) * (item=”Desk”) * (units>10))

You can also use the SUMPRODUCT function to count rows meets multiple criteria.

The result is the same as the previous example.

Note: If you prefer COOUNTIFS, use the formula below:

=COUNTIFS(C3:C15,”West”,D3:D15,”Desk”,E3:E15,”>10″)

This formula directly counts the rows that match the given criteria without needing to filter the data.

Download the practice file.