Search code examples
excelexcel-formula

Excel TextSplit a range and countif

ive been trying to figure out this probelm for a while, i have a bunch of codes and each "day" i input the codes in which each person used separated by a "," for example

Name    Codes
James   1,2
Beth    2,3,4
Charlie 1,3,5
Holly   6,8,9
Sofie   1,CR
Jimmy   2,A,CR

I am them trying to count all the codes in that range so for exmaple:

Code   Expected Total
A      1
CR     2
1      3
2      3
3      2 
4      1 
5      1
6      1
7      0
8      1
9      1

I have tried CountIF and textsplit. With google sheets you could use the ArrayFormula in combination with textsplit. However after moving to excel im not sure.

This is the formula i used for google sheets.

=IFERROR(SUM(ARRAYFORMULA(IFERROR(IF({SPLIT($E$7:$E$75,",")}=$O9,+1,+0),+0))),0)
Solution

  • Count Matches in Delimited Data

    enter image description here

    =LET(slData,B2:B100,dlData,D2:D12,dlm,",",
    sl,TEXTSPLIT(TEXTJOIN(dlm,,slData),,dlm),
    dl,dlData&"",
    dr,BYROW(dl,LAMBDA(r,SUM(--(sl=r)))),
dr)