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.