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,

 

[ANONYMOUS123456]

Yeah I understand your point of concern. Can you please observe the following pic again and tell me why the formula =SUMPRODUCT(--EXACT($A$1:$A$9,A1) is not calculating properly ?

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

 

[Tetra201]

Yeah I understand your point of concern...

No concerns whatsoever.

... Can you please observe the following pic again and tell me why the formula =SUMPRODUCT(--EXACT($A$1:$A$9,A1) is not calculating properly ?

The observed discrepancy between the Find-and-Replace and the formula's result can be caused by a number of factors, for example:
- the range in the formula does not cover your entire data range;
- other "pc < software >" entries have trailing spaces or invisible characters, etc.

Try creating a small mock-up dataset and experimenting with the entries and the formula.

 

[snjpverma]

Anony

try if the below works.
=SUMPRODUCT(--(ISNUMBER(FIND(A1,$A$1:$A$9))))

 

[ANONYMOUS123456]

No concerns whatsoever.


The observed discrepancy between the Find-and-Replace and the formula's result can be caused by a number of factors, for example:
- the range in the formula does not cover your entire data range;
- other "pc < software >" entries have trailing spaces or invisible characters, etc.

Try creating a small mock-up dataset and experimenting with the entries and the formula.

Well, I have checked that the formula covers the whole range. I have already trimmed the data so no chance for the trailing spaces. I don't know what do you mean by invisible characters.

 

[Tetra201]

... I have already trimmed the data so no chance for the trailing spaces. I don't know what do you mean by invisible characters.

For example, CHAR(160). It is not removed by TRIM() and CLEAN().
Test all your "pc < software >" entries with LENGTH() -- if it's more than 13, than you have invisible characters.

 

[ANONYMOUS123456]

For example, CHAR(160). It is not removed by TRIM() and CLEAN().
Test all your "pc < software >" entries with LENGTH() -- if it's more than 13, than you have invisible characters.

Okay I checked lengths of all entries and they are exactly 13. May be there is any other problem ?

 

[Tetra201]

... May be there is any other problem ?

Apparently, there is.

In post #1, you requested that "the contents of cells should be compared wholly not partially."
EXACT() does exactly this. If it returns FALSE, than the two 13-symbol strings are not identical.
Try to figure out what the difference is, for example by getting CODE() for each symbol.
When this is known, a formula can be devised to handle the situation.

 

[Aladin Akyurek]

COUNTIF(S) and kindred aggregation functions are like MATCH and kindred functions are sensitive to tokens (chars) with special meaning as <, *, etc.

If you are trying to count the occurrences say "software", try rather:

=SUMPRODUCT(--ISNUMBER(SEARCH(D1,$A$1:$A$9)))

where D1 = software.

Note that this counts an entry like <software><software> just once.

 

[ANONYMOUS123456]

COUNTIF(S) and kindred aggregation functions are like MATCH and kindred functions are sensitive to tokens (chars) with special meaning as <, *, etc.

If you are trying to count the occurrences say "software", try rather:

=SUMPRODUCT(--ISNUMBER(SEARCH(D1,$A$1:$A$9)))

where D1 = software.

Note that this counts an entry like <software><software> just once.

Well, I am comparing entire cells with each other. In your example, I want to calculate number of cells which entirely comprise of exactly "software". Your formula calculates, number of cells which contain "software" which could be like "*software*" .
</software></software>

 

[Aladin Akyurek]

Well, I am comparing entire cells with each other. In your example, I want to calculate number of cells which entirely comprise of exactly "software". Your formula calculates, number of cells which contain "software" which could be like "*software*" .

Change D1 to: |software>

where | stands for the less than sign.<software>

If your intent is still not met after this modification, try to post a small sample here in an Excel readable form (but not an image as you did above).</software>