+ Reply to Thread
Results 1 to 14 of 14

How to Count text using formula

  1. #1
    Registered User
    Join Date
    02-02-2014
    Location
    Jakarta
    MS-Off Ver
    Excel 2007
    Posts
    7

    Talking 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. #2
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,718

    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
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Registered User
    Join Date
    02-02-2014
    Location
    Jakarta
    MS-Off Ver
    Excel 2007
    Posts
    7

    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. #4
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,718

    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. #5
    Registered User
    Join Date
    02-02-2014
    Location
    Jakarta
    MS-Off Ver
    Excel 2007
    Posts
    7

    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. #6
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,374

    Re: How to Count text using formula

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

  7. #7
    Registered User
    Join Date
    02-02-2014
    Location
    Jakarta
    MS-Off Ver
    Excel 2007
    Posts
    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. #8
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,374

    Re: How to Count text using formula

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

  9. #9
    Registered User
    Join Date
    02-02-2014
    Location
    Jakarta
    MS-Off Ver
    Excel 2007
    Posts
    7

    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
    Last edited by Aladin45; 02-02-2014 at 12:15 PM.

  10. #10
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,374

    Re: How to Count text using formula

    It works for me. See attachment.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    02-02-2014
    Location
    Jakarta
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: How to Count text using formula

    Quang PT your formula works, when i put on your worksheet. Maybe this attachment could help you to figure out.
    Attached Files Attached Files

  12. #12
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,374

    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. #13
    Registered User
    Join Date
    02-02-2014
    Location
    Jakarta
    MS-Off Ver
    Excel 2007
    Posts
    7

    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. #14
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,374

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Count and average formula needed to not count text field
    By excelteam777 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-07-2013, 05:30 PM
  2. [SOLVED] text count issue, it seems to count the formula also?
    By randypang in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-28-2013, 03:27 AM
  3. [SOLVED] formula that will count text
    By ensmith in forum Excel General
    Replies: 7
    Last Post: 07-17-2012, 05:32 PM
  4. [SOLVED] Formula text count
    By darrelll@earl-ind.com in forum Excel General
    Replies: 2
    Last Post: 11-04-2005, 09:06 PM
  5. Replies: 1
    Last Post: 08-29-2005, 05:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1