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