# Google Sheets - countif with dynamic range

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

``````ID  Apr   May      Jun
``````

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.

Here's one way you can do it with a single formula:

``````={"ID","Complete";A2:A,index(ifna(if(regexmatch(vlookup(A2:A,
{A2:A,transpose(query(transpose(B2:D),,9^9))},2,),
"Complete"),"Y","N")))}
`````` Here's another way you can do it with a dragdown formula:

``````=if(sum(filter(--(B\$2:D="Complete"),A\$2:A=F2)),"Y","N")
`````` 