2

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.

Improve this question
0

1 Answer 1

Reset to default
4

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))))
Improve this answer
4
  • 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!
    – Vidar S. Ramdal
    Jan 7, 2015 at 8:21
  • 1
    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.
    – Vidar S. Ramdal
    Jan 8, 2015 at 21:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.