MrExcel Publishing
Your One Stop for Excel Tips & Solutions

countif identical cells


Posted by Guy Stark on September 12, 2001 3:59 PM

How do I create a formula to count all the cells that
have the same content in a range of cells?


Posted by Jaime on September 12, 2001 9:43 PM

Not sure what u mean.

If u want to count the unique cells in a range use this array function =SUM(1/COUNTIF(A1:A15,A1:A15)). Remember, this is an array function. So type cntrl+shft+enter when you finish typing it.

If you just want the number of cells in range A that have the same value as some cell in range B, then use this =SUM(COUNTIF(A1:A15,B1:B3)). Also an array function so remember cntrl+shft+enter.

Posted by Guy Stark on September 20, 2001 9:43 AM

Thanks Jaime,

Specifically what I have to do is count the number
of times a tv show has played in a regularly
scheduled slot. So I want to count the number
of times a range of cells has the
same name.
e.g. C15 = October; D15 = September;
E15 = September; F15 = (empty cell)
I'd like the value to be 2.
Because there are 2 cells with the same name
("September").
The ranges will be 4-5 cell long, and I'm not
sure how a formula might deal with "ties" (e.g.
two "Septembers" and two "Octobers"), probably
with a value of two, which would be ok.