A few words of thanks would be greatly appreciated.

Excel - Count rows where 2 conditions are true

Latest update on November 21, 2011 at 11:35 AM by aakai1056 .

Issue
Solution
Note that

Issue

I am having problems counting the # rows of problem reports where the priority = 1 and the PR is active.
here is the array:

PR PRIORITY ACTIVE   
1 2 Y   
2 1   
3 2 Y   
4 1   
5 1 Y   
6 2   
7 1 Y

I simply want to show that there are 2 problem reports active that are priority = 1.

I have tried many things, including:

IF($PRIORITY$1:$PRIORITY$7=1,COUNTIF($ACTIVE$1:$ACTIVE$7,"=Y"))

This is simplified but the principle is the same. Please advise

Solution

Try with this formula (Column A containing PRIORITY, Column B containing ACTIVE):

```
=SUMPRODUCT((A1:A7=1)*(B1:B7="Y")) 
```

Note that

Thanks to aquarelle for this tip on the forum

Excel count rows where 2 conditions are true
COUNT # ROWS where 2 conditions are true ✓ - Forum - Excel
Excel copy row if condition met ✓ - Forum - Excel
Excel - Copy rows from sheet 1 to sheet 2 - How-To - Excel
Excel - Count 2 values in one column and 1 value in other - How-To - Excel
Excel - Removing different rows in 2 sheets ✓ - Forum - Excel

This document, titled « Excel - Count rows where 2 conditions are true », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).

How to Compare Two Excel Sheets and Combine Unique Data

Conditional Formatting (IF) Using Dates on Excel

Issue

Solution

Note that

Subscribe To Our Newsletter!