Count If Range Matches Lookup in another tab

Question

I'm looking to do as the title says - I'll give an example because I don't think I did a good job of describing this situation.

Tab 1 has a list of names in column A (employees working on X day)

A Person1 Person2 Person3

Tab 2 has a list of names in column A (all company employees, so some people aren't on tab 1), and a list of attributes in column B

A B Person1 Attribute1 Person2 Attribute1 Person3 Attribute2 Person4 Attribute2 Person5 Attribute1

I'd like to have a cell that counts how many people from tab 1 column A match an attribute from tab 2 column B. In other words, using the above data as an example, if I want to count how many people listed in tab 1 have Attribute1, it would return 2, since Person1 and Person2 are on tab 1 and have Attribute1, but Person5, who has Attribute1, is not on tab 1.

I'm still not sure I correctly described my issue, but hopefully one of you helpful folks understands what I'm trying to do. Any help is appreciated!

I tried using COUNTIF, COUNTIFS, and combining COUNTIF & VLOOKUP, but I'm just sort of lost and out of my element. Usually these sort of issues are solved with a simple google search - maybe I'm just putting the wrong words into google.

I tagged this as Google Sheets & Excel because I could do this in either, if one has a better solution.

Answer 1

You may try:

=countifs(B:B,D1,index(xmatch(A:A,'Tab 1'!A:A)^0),1)