Google-sheets – How to summarize many-to-one table in Google Sheets

formulasgoogle sheets

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]

Best Answer

Please try: 

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

in Animal Types B2, copied down.

Related Topic