+ Reply to Thread
Results 1 to 4 of 4

Counting number of cells containing specific letter excluding duplicate data after filter

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

    Counting number of cells containing specific letter excluding duplicate data after filter

    Hi everyone, I am having a trouble making the formula that can count number of cells

    firstly i need formula that will perform filtering <- (is that even possible by the way?)

    and then start counting cells...if the cells contain, for example, "*"&"AB-01"&"*", <---those letters

    but i want to exclude the ones that are duplicate.

    if formula find AB-01 from 5 different cells in one column, i want formula show me "1" instead of "5".

    i have found a few formulas but none of them are working

    =SUMPRODUCT(1/COUNTIFS(range, range))

    i think this one is the best i can come up with...but it does not work..can you please advise me what i did wrong in the formula? is there a better way to do it?

    please find attached for better understanding

    and thank you for your time and help!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    2010
    Posts
    2,063

    Re: Counting number of cells containing specific letter excluding duplicate data after fil

    Your example is unclear if you just want to count the AB-01
    =COUNTIF(C22:C38,"*"&"AB-01"&"*") it will pay no account to filters and give 7 as pays no attention to filter

    On the basis of https://www.extendoffice.com/documen...ered-data.html

    =SUMPRODUCT(SUBTOTAL(3,OFFSET(C22:C38,ROW(C22:C38)-MIN(ROW(C22:C38)),,1)),ISNUMBER(SEARCH("AB-01",C22:C38))+0)
    may be what you require

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

    Re: Counting number of cells containing specific letter excluding duplicate data after fil

    i am sorry for not being too clear on that question, i actually wanted to count different types of cells that contained "AB-01" i changed the test file accordingly. it should give me 3 not 6 thats my mistake. because there are 4 AB-0123, 1 AB-0144 and 1 AB-0156. i wanted to count items without duplicates
    Attached Files Attached Files

  4. #4
    Forum Expert Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    3,811

    Re: Counting number of cells containing specific letter excluding duplicate data after fil

    Please try

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

    and Press Ctrl+Shift+Enter


    If you have Dynamic Array then

    =SUM(--ISTEXT(UNIQUE(FILTER(MID(C2:C18,FIND("AB-01",C2:C18),7),SUBTOTAL(3,OFFSET(A1,ROW(A2:A18)-ROW(A1),))))))
    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. [SOLVED] Counting the number of cells in a column which contain specific text
    By tryingtocoded in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-25-2019, 10:04 PM
  2. Replies: 9
    Last Post: 05-15-2019, 10:55 AM
  3. [SOLVED] Counting cells with specific text excluding if date in specific cells
    By FraserMc97 in forum Excel General
    Replies: 2
    Last Post: 04-07-2017, 06:19 AM
  4. [SOLVED] Counting unique cells - totally excluding duplicate values
    By ChanceLipscomb in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 05-26-2016, 12:44 PM
  5. Formula For Counting the Number of Cells Containing a Specific ID Barcode
    By ChazLeroy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-04-2013, 01:07 PM
  6. Counting number of populated cells in a specific column
    By SHS in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-07-2009, 02:37 PM
  7. excluding data which ends with letter
    By treborharris in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 11-20-2007, 07:20 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