Countif Unique Text Value only based on multiple criteria

[sfarr]

I'm using Excel 2010. I know many of you will think this is very elementary question; however, I've tried and tried and cannot seem to get this to work.

I am trying to count the number of unique businesses (column A) which also meets the criteria of active status (in column G) and type of registration (in column F). I've got about 500 rows of data but trying to test on only 20 rows.

I've tried this

=SUMPRODUCT((F2:F20="Comm")*G2:G20="active")/IF(COUNTIFS(A2:A20,A2:A20,F2:F20,"Comm",G2:G20,"Comm")=0,1,COUNTIFS(A2:A20,A2:A20&"",F2:F20,"Comm",G2:G20,"active")))


It always seems to return a value that is 1 off. I'm very novice so any help and direction would be much appreciated. Thank you for taking time to address a problem that must seem elementary to many of you.

 

[jasonb75]

See if this works

=SUM(IFERROR(1/COUNTIFS(F2:F20,F2:F20,F2:F20,"Comm",G2:G20,G2:G20,G2:G20,"Active",A2:A20,A2:A20),0))

Array confirmed with shift ctrl enter.

 

[sfarr]

Jason,

I tried this with one set of criterion and I believe it works. I'll test again with another... but couldn't waste a minute before saying a most grateful thank you for taking the time to answer my post. You're stellar.

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($A$2:$A$20<>"",IF($F$2:$F$20="comm",
    IF($G$2:$G$20="active",MATCH($A$2:$A$20,$A$2:$A$20,0)))),
    ROW($A$2:$A$20)-ROW($A$2)+1),1))

Motivated by: http://www.mrexcel.com/forum/excel-questions/292473-performance-formulas-unique-count.html