+ Reply to Thread
Results 1 to 3 of 3

counting specific texts on filter without duplicate

  1. #1
    Registered User
    Join Date
    08-06-2019
    Location
    sacramento
    MS-Off Ver
    365
    Posts
    21

    counting specific texts on filter without duplicate

    Hi Guys,

    I have hard time figuring out counting certain texts that is including "AB-01" with filter on, any texts that including "AB-01" so i applied "*"&"AB-01"&"*" but i know formula is not enough

    this is what i tried; '=SUMPRODUCT(1/COUNTIF(C3:C17, "*"&"AB-01"&"*"))'

    especially when when specific text appears more than once in one cell..

    also the specific text appears more than once, i do not want the formula to count the duplicate text

    lastly, it should recognize the filter effect and adjust the number of count

    i have attached the excel file to help you understand the problem

    greatly appreciate for your help..
    Attached Files Attached Files

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: counting specific texts on filter without duplicate

    Hi leewk,

    You can get how many cells have that string in them with:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    But if you want to remove duplicates you'll need to break them out of their combined strings.

    You might try Text To Columns but you have that silly "DO-05 together AB-0123" which wouldn't split.
    How would you count if two of those strings were in the same cell and the same?

    Perhaps some VBA and a Scripting Dictionary add-in?

    Good luck.
    (Now that I've said that, watch one of the Smart Guru's whip out an easy formula???)
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: counting specific texts on filter without duplicate

    Please try at
    D21
    Formula: copy to clipboard
    Please Login or Register  to view this content.



    D23
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.


    Or dynamic Array
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

+ 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. Replies: 3
    Last Post: 03-19-2020, 01:56 PM
  2. Replies: 11
    Last Post: 04-09-2019, 09:50 AM
  3. [SOLVED] Counting list of specific texts inside the cells
    By Sekars in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-08-2016, 04:08 AM
  4. Counting texts separated by a comma
    By Elainefish in forum Excel General
    Replies: 13
    Last Post: 01-12-2016, 08:33 PM
  5. [SOLVED] counting Duplicate values as one for a specific date
    By Shellybelly in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 06-06-2014, 11:06 AM
  6. Find Cell which some texts are Duplicate
    By Bharatdubole in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-20-2012, 02:04 AM
  7. Counting texts against particular criteria
    By twig083 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-09-2010, 10:15 AM

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