In Google Sheets, how can I count occurances of specific word in rows via ArrayFormula()

Question

I can't get my head around counting specific words in rows via ArrayFormula.
Below is the image of what I would like to achieve.

I've tried counting with COUNTIFS() function but it counts occurrences in the whole dataset and not per row.

In this example Column E should have ArrayFormula to count all occurrences of "INBOX"
and
column F should have ArrayFormula to count all occurrences of "SENT"

I've resorted to having a custom function to do this, however since it is not a built-in function, it is very slow.

@marikamitsos, thank you very much, those are good examples. — Alexander Benjamin, Sep 30 '20 at 15:21

nabais · Accepted Answer · 2020-09-30T15:59:13.770

I think this answers your question:

=ArrayFormula(if(len(A2:A), countif(if(A2:D="inbox", row(A2:A)), row(A2:A)),))

The explanation is not very obvious, but here it goes:

ArrayFormula(if(something)): the array formula will run the if statement for each row:
if(len(A2:A), something, ): will check if the first value of each row (A cell) is not empty, if it has any value, run the formula, if not, then don't run (notice the comma in the end, before the closing bracket , ). A possible alternative is: if(not(isblank(A2:A)), something, )
countif( something ): will count the number of occurrences according to a condition.
if(A2:D="inbox", row(A2:D)) - here comes the interesting part, because this formula will assign the value of the row it is in, if it finds any occurrence of the text "inbox" in it. An example is possible to see if we removed the countif() from the formula, the result would be: As it is possible to see, each value that was part of that if(A2:D="inbox", row(A2:D)) will be assigned the value of the row.

And when we gather the last 2 formulas, we have the following:

countif(if(A2:D="inbox", row(A2:A)), row(A2:A))

This means that we will count how many times the value 2 showed up (only existant in row 2), the value 3 showed up (only on row 3), and so on

Fantastic, this works. @nabais, could you explain how the grouping by row works in this formula? Thank you very much — Alexander Benjamin, Sep 30 '20 at 15:21
If the A cell is not empty, it will look for cells A to D in each row and sum the counts of the cells that have the "inbox" text in it — nabais, Sep 30 '20 at 15:24
@AlexanderBenjamin, updated the answer to give a much more detailed explanation — nabais, Sep 30 '20 at 15:59
that explanation is very good. Thank you very much. Now I understand what is happening. Clever approach. Thank you again :) — Alexander Benjamin, Sep 30 '20 at 17:22

score 0 · Answer 2 · answered Sep 30 '20 at 13:47

0

Why not using COUNTIF?

with the formula:

=COUNTIF(A2:D2, "INBOX")

answered Sep 30 '20 at 13:47

Kessy

1,894
1
8
15

Hi Kessy, that would work if you copied the formula into every cell/row. The task is to achieve the same via ArrayFormula. So COUNTIF woud be in the cell of the first row. =ArrayFormula(COUNTIF(A2:D2, "INBOX")) However this does not work. – Alexander Benjamin Sep 30 '20 at 13:59

In Google Sheets, how can I count occurances of specific word in rows via ArrayFormula()

2 Answers2