Counting in Excel Column

[ANONYMOUS123456]

Hello Everyone,
I have an excel column containing some entries. I want to check how many times each entry of the column appears in that whole column.

E.g if the A1 CELL contains "Elephant" and A2 CELL contains "Elephant Rabbit" then the count of "Elephant" in A1 should be 1 considering range A1:A2. But E.g if the A1 CELL contains "Elephant" and A2 CELL also contains "Elephant" then the count of "Elephant" in A1 should be 2 considering range A1:A2 while dragging the formula in B column.

In other words, the contents of cells should be compared wholly not partially. E.g if the A1 CELL contains "Elephant" and A2 CELL contains "Elephant Rabbit" then the count of "Elephant" in A1 should not be 2 considering range A1:A2 just because "Elephant" is existing in A2 as well. The point is cell A2 contains "Elephant" partially and "Rabbit" is also present there in A2.

Regards,

 

[Tetra201]

Will this work for you?

=COUNTIF(A:A,A1)

 

[ANONYMOUS123456]

Will this work for you?

=COUNTIF(A:A,A1)

Actually, I tested this formula but this is not working. It does partial matching.

Regards,

 

[ANONYMOUS123456]

Will this work for you?

=COUNTIF(A:A,A1)

But this formula is not working in the following scenario. Please see the following image.

 

[jeffreybrown]

Duplicate post of
https://www.mrexcel.com/forum/excel-questions/988293-counting-excel-column.html

 

[Tetra201]

Interesting.

Try this:

=SUMPRODUCT(--EXACT($A$1:$A$9,A1))

 

[ANONYMOUS123456]

Interesting.

Try this:

=SUMPRODUCT(--EXACT($A$1:$A$9,A1))

I tried the above formula in column C. "pc <software>" in A3 occurs 6 times in column A as shown in "Find and Replace" dialogue box as shown on the right but your suggested formula calculates its occurence 7 times in C3. What is your kind suggestion on that.

 

[Tetra201]

This means you have a filtered-out row with "pc < software ><software>"<software\>.</software\></software>

 

[ANONYMOUS123456]

This means you have a filtered-out row with "pc < software ><software>"<software\>.</software\></software>

But the filtered operation is not applied on column A.

 

[Tetra201]

But the filtered operation is not applied on column A.

So? The entire filtered-out row is invisible for the Find-and-Replace but it is still visible for the formula.