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.
How can I COUNT rows with ARRAYFORMULA?
google spreadsheets;formulas