In Google Sheets, what formula would let me check if all rows that have a given value in one column also have a specific value in another?

Example Sheet

I have a set of tasks with an arbitrary number of subtasks listed on Sheet1. I want the simple list of tasks on Sheet2 to indicate accurately which ones have all subtasks completed. What formula should I use to do this?

I am thinking some version of COUNTIFS, but I am not sure how to formulate it into an IF formula.

You need logical AND() operation. Try with MAP() and FILTER().

=MAP(A2:A5,LAMBDA(x,AND(FILTER(Sheet1!B2:B,Sheet1!A2:A=x))))

TO refer full column as input for large and growing dataset, try-

=MAP(A2:INDEX(A2:A,COUNTA(A2:A)),LAMBDA(x,AND(FILTER(Sheet1!B2:B,Sheet1!A2:A=x))))

One way to approach this is:

1. FILTER to collect the result of every matching T/F

2. IF statement to set the cell to either 1 or 0 depending on the T/F

3. ARRAYFORMULA to wrap the previous equations to allow for multi-cell output

4. PRODUCT to return 1 if all instances are TRUE or 0 if one or more instances are FALSE.

   =PRODUCT(ARRAYFORMULA(IF(FILTER(Sheet1!B2:B9, Sheet1!A2:A9=A2)=TRUE, 1, 0)))
   

Additionally the equation can be wrapped in another IF statement to modify the output type. i.e (1,0), (TRUE,FALSE), (RED,YELLOW), etc.

Try:

=AND(QUERY(Sheet1!$A$2:$B$9,"Select B where A = '"&A2&"'"))

Drag down below.

Result:

Explanation:

Using QUERY() to get all the data from Column A

You then modify this to only return TRUE or FALSE which is in Column B.

You then add the wrap it with AND() which will return TRUE if all conditions are true, and FALSE if at least one is false.