Count If Formula Help

[Disarmonious]

Hello,

I have a range of cells (J7:J38) that the user will type text into. I'd like to know the best way to count the cells that share the same input. For instance, let's say that the user types in "dog" in 17 cells, the word "cat" in 7 cells, the word "squirrel" in 10 cells, and "hippo" in the other cells. I want the total of the cell that is counting those cells to be 4, since there were only 4 different cells. Is there a way to have this cell count the variation of text strings without specifying what to look for?

 

[steve the fish]

Try entering this with CTRL-SHIFT-ENTER not just ENTER:

=SUM(IF(FREQUENCY(IF($J$7:$J$38<>"",MATCH($J$7:$J$38,$J$7:$J$38,0)),ROW($J$7:$J$38)-ROW($J$7)+1),1))

 

[Disarmonious]

Hello,

I get a return #VALUE ! with this formula. Is there something that I can do in trying to fix this?

 

[kweaver]

As Steve pointed out: Try entering this with CTRL+SHIFT+ENTER not just ENTER

 

[Disarmonious]

Hello,

I get the error message, "You can't enter an array formula over merged cells." I don't have merged cells, so I'd like to obtain a better understanding of this error prompt.