I have cells being imported containing words.
| Name | Location | Address | Access A | Access B | Access C... |
|---|---|---|---|---|---|
| Name1 | Location1 | Address1 | access RC | access SC | Access SC |
| Name2 | Location2 | Address2 | access SC | access SC | Access RC |
| Name3 | Location3 | Address3 | access blue SC | access RC | access red RC |
What I am trying to get is add an extra column on the right where it counts for every line the amount of times where the cell contains for ex RC and SC
Example of what i am looking for
| Name | Location | Address | Access A | Access B | Access C... | Total RC | Total SC |
|---|---|---|---|---|---|---|---|
| Name1 | Location1 | Address1 | access RC | access SC | Access SC | 1 | 2 |
| Name2 | Location2 | Address2 | access SC | access SC | Access RC | 1 | 2 |
| Name3 | Location3 | Address3 | access blue SC | access RC | access red RC | 2 | 1 |
I tried =COUNTIF(D2:F1000,"*RC*") and =ArrayFormula(IF(A2:A="","",COUNTA(F2:Y,"*RBC*"))) and =ARRAYFORMULA(if(A2:A="","",countifs(D2:D,"*RC*",E2:E,"*RC*",F2:F,"*RC*" without any luck. I want to make it as array not to have to pull down the formula. There are about 20 columns with access and about 4500 rows. Any idea how to tackle this?
CodePudding user response：
Assume your data start from 'A1', put this formula in 'G2'.
=ArrayFormula(
BYROW(D2:F,LAMBDA(ROW,
IF(INDEX(ROW,1)="","",{SUM(INT(REGEXMATCH(ROW," RC$"))),SUM(INT(REGEXMATCH(ROW," SC$")))})
))
)
It uses REGEXMATCH() to match for string end with " RC" and " SC", change the Boolean value to integer, and sum up the result.
As TRUE is equal to 1 in Excel and google sheet, this will return the number of matches.
CodePudding user response：
You were not on the wrong track with COUNTIF, you just had to add a BYROW.
Assuming your data is in A2:F, enter this formula in G2.
=ArrayFormula(BYROW(D2:F,LAMBDA(row,IF(COUNTA(row)=0,,COUNTIF(row,{"*RC*","*SC*"})))))