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

1. ## 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!

2. ## 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. ## 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

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

Formula:
`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),))))))

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