How to count rows if meets multiple criteria in Excel

In this article, we will learn how to count rows if that meets multiple criteria in Excel.

Scenario:

In simple words, while working with data tables, sometimes we need to count the cells where more than two ranges meet criteria. This can be done using the formula explained below.

How to solve the problem?

For this problem, we will be required to use the SUMPRODUCT function. Here we are given two ranges and we need the count of rows that meets 3 criteria. SUMPRODUCT function returns the SUM of corresponding TRUE values (as 1) and ignores values corresponding to FALSE values (as 0) in the returns a single array where conditions were TRUE.

Generic formula:

= SUMPRODUCT ( ( rng_1 op_1 crit_1 ) + 0 , ( rng_2 op_2 crit_1 ) + 0 , rng_2 op_2 crit_1 ) + 0 )

rng : range to look for

crit : criteria to apply

op : criteria operator, condition given as operator between range & criteria

+0 : converts boolean values to binary(0 & 1).

Example:

All of these might be confusing to understand. So, let's test this formula via running it on the example shown below.

Here we need to find the count of rows listed in the range having 3 conditions. Here we have a list of diplomatic meetings held between India & US from 2014. The table shows the President / PM with the country label and year. The table is also divided into parts representing home country and the visiting country list.

Conditions listed below:

US president "Barack Obama visited India having issues less than 2.

Use the Formula:

= SUMPRODUCT ( ( C4:C10 = "Barack Obama" ) + 0 , ( F4:F10 = "India" ) + 0 , ( G4:G10 < 2 ) + 0 ) )

C4:C10="Barack Obama" : President matching "Barack Obama" in the visiting list.

F4:F10="India" : host country matching "India".

G4:G10<2 : issues less than two.

+0 : converts boolean values to binary(0 & 1).

Here the range is given as cell reference. Press Enter to get the count.

As you can see, one time US president Barack Obama visited India which happened in 2015. This shows that the formula extracts the count of times matched in the corresponding array. As there are 1 time when the US president "Barack Obama" visited India where issues is also equals to 1 which is less than 2

With equals to Criteria:

The above example was easy. So to make it interesting we will count how many times US hosted India starting from 2014 as by data.

Conditions listed below:

US hosted India having issues is equal to 2.

Use the Formula:

=SUMPRODUCT ( ( F4:F10 = "US" ) + 0 , ( D4:D10 = "India" ) + 0 , ( G4:G10 = 2 ) + 0 )

F4:F10="US" : host country matching "US".

D4:D10="India" : visiting country matching "India".

G4:G10=2 : issues equals to two.

+0 : converts boolean values to binary(0 & 1).

Here the range is given as cell reference. Press Enter to get the count.

As you can see, there are 2 times where the US hosted India and issues equals to two. This shows that the formula extracts the count of times matched in the corresponding array. As there are 5 times when the US hosted India but issues were either 1 or 3, But here we need issues to be matched with 2.

With greater than Criteria:

Here to make it interesting we will count how many times US president "Donald Trump" hosted Indian Prime minister starting from 2014 as by data.

Conditions listed below:

US president "Donald Trump" hosted India having issues is greater than 1.

Use the Formula:

=SUMPRODUCT ( ( C4:C10 = "Donald Trump" ) + 0 , ( F4 : F10 = "India" ) + 0 , ( G4:G10 >1 ) + 0 )

F4:F10="US" : host president matching "Donald Trump".

D4:D10="India" : visiting country matching "India".

G4:G10=2 : issues equals to two.

+0 : converts boolean values to binary(0 & 1).

Here the range is given as cell reference. Press Enter to get the count.

As you can see, one time where the US president "Donald Trump" hosted India and issues greater than two. This shows that the formula extracts the count of times matched in the corresponding array. As there are 2 times when the US president "Donald Trump" hosted India but issues were either 1 or 3, But here we need issues to be greater than 1 which is 3 lies in year 2019.

 

With issues not considered in Criteria:

Here to make it easy and convenient to understand, we will count how many times in total the US president visited India starting from 2014 as by data.

Conditions listed below:

The US president visited India in total from 2014.

Use the Formula:

=SUMPRODUCT((F4:F10="India")+0,(D4:D10="US")+0)

F4:F10="US" : host country matching "US".

D4:D10="India" : visiting country matching "India".

G4:G10=2 : issues equals to two.

+0 : converts boolean values to binary(0 & 1).

Here the range is given as cell reference. Press Enter to get the count.

As you can see, 2 times where the US visited India and issues greater than two. This shows that the formula extracts the count of times matched in the corresponding array. As there was one time when the US president "Barack Obama" visited India in 2015 and one time when the US president "Donald Trump" visited India in year 2020. 

You can also perform ranges as criteria. Count the cells where 2 ranges meet criteria. Learn more about Countif with SUMPRODUCT in Excel here.

Here are some observational notes shown below.

Notes:

  1. The formula only works with numbers.
  2. The arrays in the formula must be equal in length, as formula returns error when not.
  3. The SUMPRODUCT function considers non - numeric values as 0s.
  4. The SUMPRODUCT function considers logic value TRUE as 1 and False as 0.
  5. The argument array must be the same size else the function returns an error.
  6. SUMPRODUCT function returns the sum after taking individual products in the corresponding array.
  7. Operators like equals to ( = ), less than equal to ( <= ), greater than ( > ) or not equals to ( <> ) can be performed within a formula applied, with numbers only.

Hope this article about How to count rows that meets multiple criteria in Excel is explanatory. Find more articles on Counting formulas here. If you liked our blogs, share it with your fristarts on Facebook. And also you can follow us on Twitter and Facebook. We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com

Related Articles

Find the last row of data with numbers in Excel : In a range of text values find the last of data in excel.

How to use the SUMPRODUCT function in Excel: Returns the SUM after multiplication of values in multiple arrays in excel.

COUNTIFS with Dynamic Criteria Range : Count cells depstartent on other cell values in Excel.

COUNTIFS Two Criteria Match : Count cells matching two different criteria on list in excel.

COUNTIFS With OR For Multiple Criteria : Count cells having multiple criteria match using the OR function.

The COUNTIFS Function in Excel : Count cells dependent on other cell values.

How to Use Countif in VBA in Microsoft Excel : Count cells using Visual Basic for Applications code.

How to use wildcards in excel : Count cells matching phrases using the wildcards in excel

Popular Articles

50 Excel Shortcut to Increase Your Productivity : Get faster at your task. These 50 shortcuts will make you work even faster on Excel.

How to use the VLOOKUP Function in Excel : This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets. 

How to use the COUNTIF function in Excel : Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.

How to Use SUMIF Function in Excel : This is another dashboard essential function. This helps you sum up values on specific conditions.

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube