COUNTIFS that only counts Unique entries

[RodneyW]

I'm using this formula and it works exactly as designed.

=COUNTIFS($F$8:$F$10000,"*",$A$8:$A$10000,"*")

How can I modify it so that it only counts unique entries in column A.

Thanks in advance

 

[Jeffrey Mahoney]

Since you don't have the Function Unique, I improvised by adding a helper column that counted the number of occurrences of each item in column A: B8=COUNTIF(A$8:A8,A8)

Then I could one more criteria in your formula: =COUNTIFS($F$8:$F$10000,"*",$A$8:$A$10000,"*",$B$8:$B$10000,1)

 

[Domenic]

Or, without a helper column...

=SUM(IF(FREQUENCY(IF(ISTEXT(A8:A10000),IF(ISTEXT(F8:F10000),MATCH("~"&A8:A10000&"|"&F8:F10000,A8:A10000&"|"&F8:F10000&"",0))),ROW(A8:A10000)-ROW(A1)+1)>0,1))

Note that the formula will take into consideration formula blanks ( "" ), just like your original formula.

Hope this hleps!

Edited

The formula needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

 

[RodneyW]

Or, without a helper column...

=SUM(IF(FREQUENCY(IF(ISTEXT(A8:A10000),IF(ISTEXT(F8:F10000),MATCH("~"&A8:A10000&"|"&F8:F10000,A8:A10000&"|"&F8:F10000&"",0))),ROW(A8:A10000)-ROW(A1)+1)>0,1))

Note that the formula will take into consideration formula blanks ( "" ), just like your original formula.

Hope this hleps!

I'm getting a #VALUE! error. In this formula, what is expected to be in cell A1?

 

[Domenic]

Sorry, I forgot to mention that you need to confirm the formula with CONTROL+SHIFT+ENTER, not just ENTER. And, it should return a unique count of text values, including formua blanks ( "" ).

 

[RodneyW]

Sorry, I forgot to mention that you need to confirm the formula with CONTROL+SHIFT+ENTER, not just ENTER. And, it should return a unique count of text values, including formua blanks ( "" ).

CONTROL+SHIFT+ENTER worked perfectly. Thank you

 

[Domenic]

That's great, glad I could help.

Cheers!

 

[RodneyW]

Since you don't have the Function Unique, I improvised by adding a helper column that counted the number of occurrences of each item in column A: B8=COUNTIF(A$8:A8,A8)

Then I could one more criteria in your formula: =COUNTIFS($F$8:$F$10000,"*",$A$8:$A$10000,"*",$B$8:$B$10000,1)

Thanks for the reply. I just got all of the replies here and I started with the other one and it worked perfectly