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))))
The arrayformula(query(...)) combination is not supported; there is no concept of a query processing an array of arrays or executing an array of query strings.
You have two options: (a) repeat query on every row; (b) use vlookup to retrieve columns of data, as explained below. For example:
=arrayformula(vlookup(E3:E, {A3:A, J3:J}, 2, false))
takes one element of E3:E at a time, finds this element in column A, and retrieves the corresponding element of column J. With this approach you would need six separate vlookups to get the columns J,I,H,G,F,E, but you won't need a separate command for each row.
A complication is that vlookup accepts only one search key, and you want to search by 4 parameters,
A='"&E3&"' and
B='"&B3&"' and
C="&C3&" and
D="&D3&
This can be worked around by concatenating these into one search key: you can have a column like Z,
=arrayformula(E3:E & "|" & B3:B & "|" & C3:C & "|" & D3:D)
which concatenates four search parameters into one pipe-delimited search key. Do the same for the table to be searched, and then compare these keys using vlookup.
Best Answer
Formula
=ArrayFormula(MMULT(TRANSPOSE((ROW(B1:B9)<=TRANSPOSE(ROW(B1:B9)))*ISTEXT(B1:B9)*1),ISTEXT(B1:B9)*1))What you are looking to do is called "running total".
The above formula use arrays,
TRANSPOSE,MMULT(matrix multiplication),ISTEXTand unity multiplication (to parse TRUE as 1 and FALSE as 0) instead creating aCOUNTAformula chain.The first argument of
MMULTcreates a variant of a lower triangular matrix (all the values above the main diagonal are zero). Instead of all lower values be 1, they are 1 if the corresponding values are a text values, and 0 if not.NOTE: Not all the spreadsheet functions are able to handle an array as argument.
INDIRECTis one of those functions.