# How to Count text using formula

1. ## How to Count text using formula

I like to Count text using excel formula, example:
A B C D
1 EQR/G/022/02/14
2 EQR/N/020/02/14
3 EQR/S/007/02/14
4 EQR/G/019/02/14
5 EQR/N/023/02/14
6 E-MAIL
7 E-MAIL
8 EQR/G/040/02/14

Where the answer should be 8, where E-MAIL can be counted more than one, and the other should be counted only once if the pattern is the same.

Thanks

2. ## Re: How to Count text using formula

Hi,

Can you explain a bit more what you mean by "if the pattern is the same"? What defines "the same" in your context?

Remember, you cannot tell Excel to determine for itself whether it regards two strings as being "the same" - you have to first produce one or more rigorous statements which tell us precisely what constitutes "the same".

Regards

3. ## Re: How to Count text using formula

I'll give you an example:
EQR/N/023/02/14
EQR/N/023/02/14
E-MAIL
E-MAIL

The answer Should be 3, Where E-MAIL counted twice and EQR/N/023/02/14 counted once because it have the same pattern.

Thanks

4. ## Re: How to Count text using formula

Ok, but that didn't answer my question. In your original example, where you said the result should be 8, you seem to be suggesting that:

EQR/G/022/02/14, EQR/N/020/02/14, EQR/S/007/02/14, EQR/G/019/02/14, EQR/N/023/02/14, EQR/G/040/02/14

should all be counted since they are the "same pattern", though they are clearly not identical.

So please explain on what basis two strings should be considered to have the "same pattern".

Regards

5. ## Re: How to Count text using formula

Ok, I'll give other example, with additional info:
A B C D
1 EQR/G/022/02/14
2 EQR/N/020/02/14
3 EQR/S/007/02/14
4 EQR/G/019/02/14
5 EQR/N/023/02/14
6 E-MAIL
7 E-MAIL
8 EQR/G/040/02/14
9 EQR/G/040/02/14

Such as A8 and A9, it's exactly the same pattern, so it should be counted only once, if the pattern change such A8 is EQR/G/040/02/14, and
A9 EQR/G/041/02/14 both of them should be counted. Example:

A B C D
1 EQR/G/022/02/14
2 EQR/N/020/02/14
3 EQR/S/007/02/14
4 EQR/G/019/02/14
5 EQR/N/023/02/14
6 E-MAIL
7 E-MAIL
8 EQR/G/040/02/14
9 EQR/G/041/02/14

It's should be counted 9.
Thanks

6. ## Re: How to Count text using formula

=SUMPRODUCT(1/COUNTIF(A1:A9,A1:A9))+COUNTIF(A1:A9,"E-MAIL")-1

7. ## Re: How to Count text using formula

Sory Quang PT, it doesn't work. The result is #DIV/0!. Can you give me other formulas?

Thanks.

8. ## Re: How to Count text using formula

Is there any blank cell in range? Can you post a sample worksheet?

9. ## Re: How to Count text using formula

There are no blank cell in range, it's exactly like what I posted on previous thread.

Thanks

10. ## Re: How to Count text using formula

It works for me. See attachment.

12. ## Re: How to Count text using formula

Many blank cells are found!
Anyway, try again with array formula:
``Please Login or Register  to view this content.``
Confirmed with ctrl-shift-enter

13. ## Re: How to Count text using formula

Sorry Quang PT, there are blank range in my worksheet, because of that your formula didn't work. Now It works.

Thanks.

14. ## Re: How to Count text using formula

Nice to hear it works. pls donot forget two things: marks this thread [SOLVED] and read my signature thanks.

There are currently 1 users browsing this thread. (0 members and 1 guests)