Trying to combine ARRAYFORMULA() with COUNTIF() and SPLIT()

0
votes

I'm trying to find out how many clients each of my colleagues are working on, which will then be used for other calculations. My spreadsheet has a column with the initials of each colleague, separated by commas (which I don't have any control over), and currently I can click and drag the formula

=COUNTIF(SPLIT($B2, ", "), "NameHere"),

which works quite nicely. However, I need to convert this to an arrayformula and in doing so everything breaks. What appears to be happening is that the COUNTIF is counting through the entire 2D array created by SPLIT(ARRAY, ", "), and giving a single total sum, but I would like it to count through each row, one at a time, with a sum for each row.

I've attached a spreadsheet, with the attempted arrayformula in cell O2.

(As an aside, a simpler solution would appear to be search the initials column for the colleagues initials, as they're set up to be unique, but one colleague has the initial "J" and so would appear within "LJ", and I can't see a way around this with wildcards.)

1
google-sheetsarray-formulas
Split is not a worksheet function in Excel, as as such this question is solely applicable to only google sheets. I have removed the excel tags. – Scott Craner

1 Answers

1
votes

Sample File

Text solution

Please try this formula, pasted into C2:

=ArrayFormula((LEN(B2:B24)-LEN(SUBSTITUTE(B2:B24,C1:M1,"")))/LEN(C1:M1))

Notes:

  • it replaces text, counts the length of text before and after replace, and divides the difference by the length of the search text.
  • I assume the string does not contain another string: no pair like "bo" and "boo"

Array solution

An alternative way is to use the formula:

=arrayformula(mmult( --(IFERROR(SPLIT($B$2:$B$24,",")=C1, false)), ARRAYFORMULA(SIGN(ROW(INDIRECT("a1:a"&MAX(LEN($B$2:$B$24)-len(SUBSTITUTE($B$2:$B$24,",","")))+1)))) ))

in C2 and copy it to the range C2:M2

Notes: