excelexcel-formulacountifsumproduct

Excel Functions For Conditional Count Across Multiple Columns and Exclude Duplicates

I'm doing something wrong here... I keep getting "0" as an answer for the below table, when the answer should be "2".

Goal: To Count the number of Areas (Col A) that have an "activity1" Task (Col B), with an active value of 1 (Col C), while omitting duplicate Areas (Col A) from the Count.

Formula attempted:

=SUMPRODUCT(--(A:A<>""), --(C:C<>""), --(B:B="activity1"), 1/COUNTIF(A:A, A:A))

Table Used:

Column A: Areas Column B: Task Column C: Active
100 activity1 1
200 activity1 1
100 activity1 1
100 activity2 1
Solution 
  • =ROWS(UNIQUE(FILTER(A2:A5,(B2:B5="activity1")*(C2:C5=1))))

    =ROWS(UNIQUE(FILTER(A2:A5,(A2:A5<>"")*(B2:B5="activity1")*(C2:C5=1))))