I've got two separate sheets we'll call 'Sheet A' and 'Sheet B' with a table on each.

The table on 'Sheet A' has a Many-to-one relationship to the data on the second sheet.

On the second sheet, I'd then like a summary of the data on 'Sheet A' which searches for all rows which match that particular record, then return a Yes/No response by using an 'AND' operator on each matching row.

I know this description does a horrible job at explaining the situation, so I've made the following demonstration spreadsheet (which you can see in reality):

Sheet A

Sheet A Example

Sheet B

Sheet B Example

What I'm trying to automate is the second column of the second sheet (which I've coloured in green).

Any thoughts?

[Also, feel free to let me know if you can think of better wording for this question if you understand what I'm trying to explain]

2 Answers 2


Please try:

=if(COUNTIFS('Our Animals'!$B:$B,A2,'Our Animals'!$C:$C,"no")=0,"Yes","No")  

in Animal Types B2, copied down.


Another way of doing it, is by using the following formula.


=QUERY('Our Animals'!B:C,"SELECT C WHERE B='"& A2 &"' ORDER BY C ASC LIMIT 1")


enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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