How do I count unspecified matches in a column?

[boden]

I am in need of counting the number of matches (matches in which I do not set a specific value) in a column. Example: In column A: Red, Blue, Green, Red, Blue, Yellow, Brown The answer I am seeking a formula for is 2.

I tried the =COUNTIF function, but I can't figure out what criteria to use for finding matches that I don't specify. I basically want to count any matches in that column.

If I could make up a formula, it would be something like: =COUNTIF(A2:A8,ANYMATCH) or something like that, even though that's completely wrong hahaha

 

[T. Valko]

I am in need of counting the number of matches (matches in which I do not set a specific value) in a column. Example: In column A: Red, Blue, Green, Red, Blue, Yellow, Brown The answer I am seeking a formula for is 2.

I tried the =COUNTIF function, but I can't figure out what criteria to use for finding matches that I don't specify. I basically want to count any matches in that column.

If I could make up a formula, it would be something like: =COUNTIF(A2:A8,ANYMATCH) or something like that, even though that's completely wrong hahaha

Try this array formula**.

=SUM(IF(FREQUENCY(MATCH(A2:A8,A2:A8,0),ROW(A2:A8)-ROW(A2)+1)>1,1))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

Assumes no empty cells within the range.

 

[boden]

Exactly what I needed. thanks, T.Valko

 

[AlphaFrog]

This will count anything that is more than one in Cells A1:A20

=SUM((FREQUENCY(A1:A20,A1:A20)>1)*1)

 

[T. Valko]

This will count anything that is more than one in Cells A1:A20

=SUM((FREQUENCY(A1:A20,A1:A20)>1)*1)

That will only count duplicate numbers.

 

[T. Valko]

Exactly what I needed. thanks, T.Valko

You're welcome. Thanks for the feedback!