CODEWITHSUNDEEP
google-sheetsfilter
Vasilis
Vasilis

Reputation: 63

Google Sheets filter based on two lists

I have the following situation: I have three sets of data, let's say a set of names, a type of clothing and its colour. I have one table which links a name to an article of clothing and I have another table which links a colour to an article of clothing. What I want to do is count how many pieces of clothing one person has that have a particular colour. I think I either have to find all the clothes one person has and then check for each of those which one are available in the desired colour, or I need to start by checking which clothes are available in that colour and then check if the person has any of those, but I don't know how to work with a list of results in Google Sheets. Is there a way I can achieve this?

TABLE 1                TABLE 2
Name:     Type:    |   Colour:   Type:
John      Jeans    |   Blue      Shirt
John      Shorts   |   Blue      Jeans
John      Socks    |   Blue      Socks
Geralt    Shoes    |   Red       Socks
Geralt    Shirt    |   Red       Shirt
Stephanie Socks    |   Red       Shoes
Stephanie Jeans    |   Green     Jeans
                   |   Green     Shoes

In this example, if the person I'm looking at is John and the colour I'm looking at is blue, we can see that John has jeans, shorts and socks. Blue items include a shirt, jeans and socks. Jeans and socks appear in both of these lists, so the result should be jeans and socks (which means the eventual result I'm looking for is 2, since the list is 2 items long).

Upvotes: 1

Views: 307

Answers (1)

Osm
Osm

Reputation: 2891

Solution

Use this formula or Make a copy of this example.

=COUNTA(IFERROR(FILTER(FILTER($B$3:$B,$A$3:$A=H2),
          MATCH(FILTER($B$3:$B,$A$3:$A=H2), 
                FILTER($E$3:$E,$D$3:$D=H3), false)), "No match"))

enter image description here

Demo

enter image description here

Update - handling "No match"

=COUNTA(IFNA(FILTER(FILTER($B$3:$B,$A$3:$A=H2),
         MATCH(FILTER($B$3:$B,$A$3:$A=H2), 
               FILTER($E$3:$E,$D$3:$D=H3), false))))

enter image description here

Upvotes: 1

Related Questions