Excel - Count rows where 2 conditions are true

December 2016




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

Related :

This document entitled « Excel - Count rows where 2 conditions are true » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.