# How can I COUNT rows with ARRAYFORMULA?

I have a sheet that lists incidents in an `Incidents` sheet:

``````| Date       | Name  | Incident            |
|------------|-------|---------------------|
| 01.01.2015 | Vidar | Car broke down      |
| 01.01.2015 | John  | Traffic             |
| 02.01.2015 | John  | Train delay         |
| 03.01.2015 | Vidar | Sick                |
| 03.01.2015 | John  | Dentist appointment |
``````

Now, I want to count the number of accidents per `Name` in a separate sheet called `Count`, which is simple enough:

``````| Name       | Number of incidents                                    |
|------------|--------------------------------------------------------|
| Vidar      | =COUNTA(FILTER(Incidents!B\$2:B; Incidents!B\$2:B = A2)) |
| John       | =COUNTA(FILTER(Incidents!B\$2:B; Incidents!B\$2:B = A3)) |
``````

There may be new names added to the `Incidents` list, so in order to not have to update the `Count` sheet with the new name, I have put the formula `=UNIQUE(Incidents!B2:B)` in `Count!A2`. This, at least, gives me a list of unique names.

But I still have to manually (yuck!) drag the formula from `Count!B3` to the new row.

Couldn't this be solved by an `ARRAYFORMULA` in `Count!B2`? I have tried `=ARRAYFORMULA(COUNTA(FILTER(Incidents!B\$2:B; Incidents!B\$2:B = A2:A)))`, but this gives me only a single value. I want one value for each row in the `Count` sheet.
How can I do that?

Here's an example spreadsheet that can be copied for experimentation.

I am looking to learn to be comfortable using `ARRAYFORMULA`, so answers combining my existing non-ARRAY-formula with `ARRAYFORMULA` will be prefered. Or, if it is not possible, for some reason, to use `ARRAYFORMULA` with `FILTER`, I would really like an explanation why.

In addition to previous posts, I think I would go for a query function. Although not that short , it will eliminate the need 1) to drag the current formula down (newly added rows will 'automatically be added to the output of the query) and 2) to have the unique formula to populate col A on the count sheet.

``````=query(Incidents!B2:C; "select B, count(C) where B<>''  group by B label B 'Name', count(C) 'Number of incidents' ")
``````

EDIT: Closer to the topic, if a list of names is already given (in col A), a count in arrayformula should be possible by doing:

``````=ArrayFormula(countif(split(concatenate(if(len(Incidents!B2:B),Incidents!B2:B&char(9),)),char(9),0), filter(A:A, len(A:A))))
``````
• A FILTER function isn't capable of iterating over an array. If you want an arrayformula that counts the values obtained by the unique formula (the names), then something like this should do it: =ArrayFormula(countif(split(concatenate(if(len(Incidents!B2:B),Incidents!B2:B&char(9),)),char(9),0), filter(A:A, len(A:A)))) where the unique names are in col A.
– JPV
Jan 7, 2015 at 8:14
• "A FILTER function isn't capable of iterating over an array." - interesting. If you could explain why `FILTER` is different from `COUNTIF` in that respect (and edit it into your answer), that would be great! Jan 7, 2015 at 8:21
• Filter is able to output an array, whereas COUNTIF is not (one cell output). While countif is more of an 'aggregate' function, it can be forced into an arrayformula, so that it iterates over an array (e.g. to perform a row per row count).
– JPV
Jan 7, 2015 at 8:46
• Ah, OK, so what you're saying is that since `FILTER` returns an array, it cannot be used with `ARRAYFORMULA` that way? That makes sense, I guess. If you know any official documentation that states something similar, it would be great. Jan 8, 2015 at 21:12