CODEWITHSUNDEEP
arraysexcelsearchexcel-formulacount
Diego Villagran Hernandez
Diego Villagran Hernandez

Reputation: 3

Excel count how many times a value appears in a range looking within cells

I have a column with unique emails and I need to count how many times each one is in another range with emails BUT in this one the cells can contain more than one email, and they are separated by a blank space, is there a way to count them?

It's something like this

Thank you

Upvotes: 0

Views: 217

Answers (3)

Evil Blue Monkey
Evil Blue Monkey

Reputation: 2609

You can convert the range with multiple per cell adresses into a range and then use COUNTIF to count them. Then again, if are you looking for a one step solution you can try this array formula:

=SUM((LEN($D$2:$D$11&" ")>LEN(SUBSTITUTE($D$2:$D$11&" ",A2&" ","")))*1)

This one is for cell B2. Just remember to use Ctrl+Shift+Enter when you confirm it. To be fair: this past formula will not detect any double occuring address if it's in the same cell (like "email_2 email2"). Since i can't be sure you won't encounter such eventuality, this array formula should count these cases too:

=SUM((LEN($D$2:$D$11&" ")-LEN(SUBSTITUTE($D$2:$D$11&" ",A2&" ","")))/LEN(A2&" "))

Upvotes: 0

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27233

Try the following it will give you the exact number of occurrences as per your screenshots:

enter image description here

=LET(
     _Instances, SEQUENCE(,MAX(LEN(AllEmailsList[AllEmail])-LEN(SUBSTITUTE(AllEmailsList[AllEmail]," ",))+1)),
     SUM(N(IFNA(TEXTSPLIT(TEXTAFTER(" "&AllEmailsList[AllEmail]," ",_Instances)," "),"")=[@[Unique Email]])))

Another alternative way using FIND() & COUNT()

enter image description here

=COUNT(FIND(" "&[@[Unique Email]]&" "," "&AllEmailsList[AllEmail]&" "))

Upvotes: 1

Rory
Rory

Reputation: 34045

Try:

=SUMPRODUCT(--ISNUMBER(SEARCH(" "&[@[Unique email]]&" "," "&othertable[allEmail]&" ")))

If you know that there will not be situtations where one email address is also part of another email address in your unique list, you could simply use countif:

=COUNTIF(othertable[allEmail],"*"&[@[Unique email]]&"*")

Upvotes: 1

Related Questions