Search code examples
regexif-statementgoogle-sheetsgoogle-sheets-formulaarray-formulas

Google Sheets ARRAYFORMULA count preceeding rows that meet condition

Let's say I have a spreadsheet that looks something like this:

Name                   D-List
---------------------  ------
Arnold Schwarzenegger
Bruce Willis
Dolph Lundgren
Dwayne Johnson
Jason Statham
Keanu Reeves
Samuel L. Jackson
Sylvester Stallone
Vin Diesel

For the D-List column, I'd like to count the number of proceeding rows that contains the string "d". If the row doesn't contain a "d", then I want it to return an empty string.

For any given row, I can get this to work with the following pseudo formula:

=IF(REGEXMATCH(A<row>, "d"), COUNTIF(A<row>, "*d*"), "")

Name                   D-List
---------------------  ------
Arnold Schwarzenegger  1
Bruce Willis    
Dolph Lundgren         2
Dwayne Johnson         3
Jason Statham   
Keanu Reeves    
Samuel L. Jackson   
Sylvester Stallone  
Vin Diesel             4

I can turn this into an expression that can be duplicated between rows by using INDIRECT and ROW:

=IF(REGEXMATCH(A2, "(?i)d"), COUNTIF(INDIRECT("A2:A" & ROW(A2)), "*D*"), "")

Name                   D-List
---------------------  ------
Arnold Schwarzenegger  1
Bruce Willis    
Dolph Lundgren         2
Dwayne Johnson         3
Jason Statham   
Keanu Reeves    
Samuel L. Jackson   
Sylvester Stallone  
Vin Diesel             4

However, if I try to stick it in an ARRAYFORMULA, it doesn't work.

=ARRAYFORMULA(IF(REGEXMATCH(A2:A, "D"), COUNTIF(INDIRECT("A2:A" & ROW(A2:A)), "*D*"), ""))

Name                   D-List
---------------------  ------
Arnold Schwarzenegger   
Bruce Willis    
Dolph Lundgren         1
Dwayne Johnson         1
Jason Statham   
Keanu Reeves    
Samuel L. Jackson   
Sylvester Stallone  
Vin Diesel             1

What am I missing?

Solution

  • try:

    =ARRAYFORMULA(IF(
 REGEXMATCH(A2:A, "(?i)d"), COUNTIFS(
 REGEXMATCH(A2:A, "(?i)d"), 
 REGEXMATCH(A2:A, "(?i)d"), ROW(A2:A), "<="&ROW(A2:A)), ))

    0