Google Sheets – How to Skip Empty Cells with Consecutive Numbering Using ArrayFormula

formulasgoogle sheetsgoogle-sheets-arrayformula

I am trying to auto-number only the existing names on a cell using an arrayformula:

I have two cells one for the names and the other for consecutive numbers.

Here I have an example of what I want to achieve:

The current formula still counting the empty spaces, but it only should count the ones with content:

=ARRAYFORMULA(if(ISBLANK(A2:A12),"",row(Z1:Z12)))

Here is an ScreenShot:

Best Answer

Try

=ARRAYFORMULA(IFERROR(MATCH(A2:A12&ROW(A2:A12),FILTER(A2:A12&ROW(A2:A12),A2:A12<>""),0)))

Related Solutions

Google Sheets – Conditional Cumulative Sum Using Arrayformula in Google Sheets

If the data starts in row 2, then try:

=ArrayFormula(IF(A2:A="A",SUMIF(IF(A2:A="A",ROW(A2:A),ROWS(A:A)+1),"<="&ROW(A2:A),B2:B),))

Google-sheets – How to COUNT rows with ARRAYFORMULA

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))))

Best Answer

Related Solutions

Google Sheets – Conditional Cumulative Sum Using Arrayformula in Google Sheets

Google-sheets – How to COUNT rows with ARRAYFORMULA

Related Topic