If I have a table that looks like the below. How can I get excel to return the breed of dog that only meets the criteria Is_Brown = 0 and never meets the criteria Is_Brown = 1. For example in the case below that would be the Golden Retriever and Bulldog.
| Dog Breed | Is_Brown |
|---|---|
| Labrador | 1 |
| Spaniel | 1 |
| Poodle | 0 |
| Labrador | 0 |
| Bulldog | 0 |
| Labrador | 1 |
| Golden Retriever | 0 |
| Poodle | 1 |
| Spaniel | 0 |
| Golden Retriever | 0 |
| Bulldog | 0 |
If I try to filter or use a pivot table and filter on Is_Brown = 0 this would still include those breeds where for that specific row the filter criteria was met but I want the Dog Breed where the criteria applied is ONLY ever met across all rows.
CodePudding user response：
For your specific example, you can use a pivot table with Breed as rows and Is_Brown as summed values.
Then the breeds you look for are the ones for which the sum is nil, you just filter the rows by value:
Which gives you the expected output:
CodePudding user response：
If you are using Office 365, this should work (assuming breed is column A and is_brown is column B :
=FILTER(UNIQUE(A2:A12),SUMIF(A2:A12,UNIQUE(A2:A12),B2:B12)=0)
UNIQUE(A2:A12): Returns an array of breeds without duplicatesSUMIF(A2:A12,UNIQUE(A2:A12),B2:B12): Returns an array of the sum of "is_brown" for each breed=FILTER(UNIQUE(A2:A12),SUMIF(A2:A12,UNIQUE(A2:A12),B2:B12)=0): Returns the breed for witch the sum of is_brown is 0
If you are using a table this would also work (assuming your table is called Table1) :
=FILTER(UNIQUE(Table1[Dog Breed]),SUMIF(Table1[Dog Breed],UNIQUE(Table1[Dog Breed]),Table1[Is_Brown])=0)