google sheets - Count If Range Matches Lookup in another tab - Stack Overflow

admin管理员组

文章数量:1419243

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.

• google-sheets

Share Improve this question Follow edited Jan 29 at 13:19 Mayukh Bhattacharya 27.8k99 gold badges2929 silver badges4242 bronze badges asked Jan 29 at 11:33 sherbertherbertsherbertherbert 311 bronze badge 1

• Google Sheets and Excel are 2 different platforms. Please clarify which one you are using. Also please use a Markdown table to describe your dummy data. – Babanana Commented Jan 29 at 11:56

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 2

You may try:

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

Using Reduce to Count

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

本文标签： google sheetsCount If Range Matches Lookup in another tabStack Overflow