0

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.

1 Answer 1

0

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

enter image description here

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

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.