Arrayformula to count instances of specific text in a row

Solved

Long story short, I used copyDown to copy formulas into new Google Form submissions in my Sheet. I have a Sheet with multiple tabs and multiple forms inputting data and using copyDown. The addon was only intended to work with one tab; and it finally stopped working on me. I have been updating the formulas info arrayformulas that achieve the same thing as copyDown.

One arrayformula I'm trying to figure out is one that checks a row, let's say A2:M2, for all instances of the text "Almost Always" and returns the number of occurrences. Previously, using the addon, it was just a simple COUNTIF formula. I haven't been able to get that into an arrayformula that works on multiple rows/as new submissions come in.

Is this possible?

Posted Sep 30, 2017, 3:45 PM UTC

18
AndroidMasterZ14•

Could you post your sheet or a sample copy of it?

COUNTIF should work the same..

2
jnuclearOP•

Thank you for the quick response. Sure. I'm chasing the kid around and will post it when I get back in.

1
jnuclearOP•

Here is a link to a sample: https://goo.gl/RXbjoR

Countif seems to count for all the values of every row and return it to the cell H2. I'm hoping to have it spit a count for each row.

1
jnuclearOP•

I did some digging and found a solution. Thanks!

1
16495701722mod•

Would you mind posting your solution here so that others can see it?

2
jnuclearOP•

No problem!

1
jnuclearOP•

=ArrayFormula(COUNTIF(if(F2:AJ="Almost Always", row(F2:AJ)),row(F2:AJ)))

1
jnuclearOP•

Not sure what exactly the row part is doing that makes this work considering the then/else appear to be doing the same thing; but it counts the instances of "Almost Always" and gives me the results per row:

=ArrayFormula(COUNTIF(if(F2:AJ="Almost Always", row(F2:AJ)),row(F2:AJ)))

1