How do I get the following formula to work when referencing a closed workbook?

Hello,

=SUM(--(FREQUENCY(IF(($B$1:$B$10000=1),COUNTIF($A$1:$A$10000,"<"&$A$1:$A$10000),""),COUNTIF($A$1:$A$10000,"<"&$A$1:$A$10000))>0)) CTRL+SHIFT+ENTER

The above code counts unique values in column A if a value of 1 is in column B. It correctly ignores blank values which is why I prefer this method to others I found on the internet. However it is my understanding that the CountIf function doesn't work when referencing closed workbooks. How would I get the code above to work when referencing a closed workbook?

Thank You,
HumbleAko

Originally Posted by Tony Valko

Try this...

=SUM(IF(FREQUENCY(IF(B1:B20=1,IF(A1:A20<>"",MATCH(A1:A20,A1:A20,0))),ROW(A1:A20)-ROW(A1)+1),1))

Array entered.

Not only will the above syntax work on a closed file, it's also more efficient than using COUNTIF.

Hi Tony Valko,

It works perfectly. Thank you!