3

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)
Ben Parkes
  • 53
  • 5
  • There is no `A` in the codes list also the counts are not matching, since `2` will also be counted thrice are those typos, also i don't see `7` in the codes list? – Mayukh Bhattacharya Aug 11 '23 at 23:46
  • Why are you using countifs() instead of count()? Also there are other ways to split the values see this example https://stackoverflow.com/q/72210897/5091720 – Shane S Aug 11 '23 at 23:46
  • @MayukhBhattacharya Yes sorry those were typos, the 7 is just there for an example where nobody used that code. – Ben Parkes Aug 11 '23 at 23:54
  • Ok, so you already have a list of codes and you are needing the counts from that range right? – Mayukh Bhattacharya Aug 11 '23 at 23:56
  • @MayukhBhattacharya, if there was a code for "17" both 17 and 7 would have 1 – Ben Parkes Aug 12 '23 at 00:24
  • 1
    The sample seems flawed. Explain why there is a 7 in the end result while it's not in the data, why does this differ from say; 10, 11 etc? Explain, explain, explain, don't leave your readers to guess. – JvdV Aug 12 '23 at 06:28

3 Answers3

3
=LET(a,  A10:A20,
     b,  "," & B2:B7 & ",",
MMULT(
      --ISNUMBER(FIND(","&a&",",TOROW(b))),
      SEQUENCE(ROWS(b),,,0)))

enter image description here

P.b
  • 8,293
  • 2
  • 10
  • 25
2

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)
VBasic2008
  • 44,888
  • 5
  • 17
  • 28
1

You could try either of the formulas, it needs MS365 to work with:

enter image description here

• Formula used in cell E2

=LET(
     a,TOCOL(TAKE(A2:B100,,-1),1),
     b,TOCOL(D2:D100,1),
     BYROW(b,LAMBDA(x,
     SUM(--(DROP(REDUCE("",a,LAMBDA(x,y,VSTACK(x,TEXTSPLIT(y,,",")))),1)=TEXT(x,"@"))))))

Or,

• Formula used in cell F2

=LET(
     a,TOCOL(TAKE(A2:B100,,-1),1),
     b,TOCOL(D2:D100,1),
     MAP(b,LAMBDA(α, SUM(--(TOCOL(TEXTBEFORE(TEXTAFTER(","&a,",",SEQUENCE(,MAX(LEN(a))))&",",","),3)=TEXT(α,"@"))))))

A complete screenshot showing the formulas are working :

enter image description here

Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32
  • Thankyou for the help, obviously the columns A and B are dynamic, so the B2:B12 i would like to be B2:B100 for example, however there may be some empty rows? this appears to break both formulas – Ben Parkes Aug 12 '23 at 00:13
  • Yeah so the column B is currently 99 long from B2:B100, however the last 60 or 70 cells may be blank. – Ben Parkes Aug 12 '23 at 00:17
  • Neither of these work, keep getting a calc error. For example column A may be populated with a name, column B can be populated with codes if column A has a name but may not have any codes (user didnt use a code). – Ben Parkes Aug 12 '23 at 00:34
  • @BenParkes neither of these works is not right to say because i have already shown a screenshot with the data you have posted that it is working on my end, if you are not able to work out and getting to work, then please post some screenshot to see or an excel link copy – Mayukh Bhattacharya Aug 12 '23 at 00:37
  • @BenParkes please refer my screenshot. If it is not working on your end, then kindly show some screenshot. – Mayukh Bhattacharya Aug 12 '23 at 00:42
  • 1
    @BenParkes What you post in the comments as critics to the answer are actual critics to your question. If it wasn't in the question, how can you say we implemented it incorrectly? – P.b Aug 12 '23 at 19:19