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.
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.
You may try:
=countifs(B:B,D1,index(xmatch(A:A,'Tab 1'!A:A)^0),1)
There are several ways to accomplish this, one way I could recommend doing is utilizing Filter and Match for Cross Matching then using Reduce to count.
Sample Data
Sheet 1
| Person1 |
|---|
| Person2 |
| Person3 |
Sheet2
| Col A | Col B | Col C | Col D |
|---|---|---|---|
| Person1 | Attribute1 | Attribute1 | 2 |
| Person2 | Attribute1 | ||
| Person3 | Attribute2 | ||
| Person4 | Attribute2 | ||
| Person5 | Attribute1 |
Col C is your manually inputted reference, depending on which attribute you are looking for, and Col D is where I put the formula recommended.
Sample Formula
=REDUCE(0, FILTER(A2:A6, B2:B6 = C2), LAMBDA(a,c, IF(ISNUMBER(MATCH(c,Sheet1!A1:A3,0)),a+1,a)))
Note: I tested this on both of the platforms
References
Match - Excel
Filter - Excel
Reduce - Excel
Match - Google Sheets
Filter - Google Sheets
Reduce - Google Sheets