Count If Formula Help

Disarmonious

Board Regular
Joined
Oct 31, 2016
Messages
139
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?
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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))
 
Upvote 0
As Steve pointed out: Try entering this with CTRL+SHIFT+ENTER not just ENTER
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top