Google Sheets - countif with dynamic range

Question

In sheet1 have a column with a list of IDs, each with a set of statuses:

ID  Apr   May      Jun
1   Load  Load     Complete
2   Load  Complete NA
3   Load  Load     Load

In a separate sheet, sheet2 I have a list of the IDs and I am trying to show which of those are complete. My thinking is to use countif with a dynamic cell range using the address function:

=countif("'sheet1'!"&address(row(index('sheet1'!$A$1:$A$5,match(A2,'sheet1'!$A$1:$A$5,0),1)),3)&":"&ADDRESS(row(index('sheet1'!$A$1:$A$5,match(A5,'sheet1'!$A$1:$A$500,0),1))+1,26),"Completed")

This formula should find the relevant row for each Id and then check to see whether there are any values of 'Completed' in that row. When combined with an if function, this should give an output of the following in sheet2:

ID Complete
1  Y
2  Y
3  N

But at the moment, it is not registering any as complete as the countif formula is returning a 0 for every row.

Is this because countif cannot handle dynamic ranges using address? If there is a better way to do this, please let me know.

Answer 1

try:

=INDEX(IFNA(VLOOKUP(F2:F, QUERY({A2:A, FLATTEN(QUERY(TRANSPOSE(B2:D),,9^9))}, 
 "select Col1,'Complete' where Col2 contains 'Complete'"), 2, )))

or:

=INDEX(IF(F2:F="",,IFNA(VLOOKUP(F2:F, QUERY({A2:A, FLATTEN(QUERY(TRANSPOSE(B2:D),,9^9))}, 
 "select Col1,'Y' where Col2 contains 'Complete'"), 2, ), "N")))

Answer 2

Given your data, please try the following

=INDEX({A2:A,IF(A2:A="","",
        (IF(REGEXMATCH(B2:B&C2:C&D2:D,"Complete")=TRUE,"Y","N")))})

(As always, please adjust ranges to your needs)

Functions used:

• INDEX
• IF
• REGEXMATCH