Way to count values which are present in another list

[Sourav Das]

How do I count a list of Values only which are present in another List?

For example:

Value(to be counted)	Value list
12	10
1	12
66	22
33	44
9	87
22
12

<tbody>
</tbody>

​

 

[Trevor G]

Welcome to the forum, you should be able to use COUNTIF see example below:

Excel Workbook
	A	B	C
1	Value(to be counted)	Value list	Count
2	12	10	0
3	1	12	2
4	66	22	1
5	33	44	0
6	9	87	0
7	22		0
8	12		0
Sheet1

 

[Sourav Das]

Thanks a Lot Trevor for the fast reply and warm welcome. But is there a way to combine the total no. of Counts in a single cell?

Welcome to the forum, you should be able to use COUNTIF see example below:

 

[Aladin Akyurek]

1. control+shift+enter, not just enter:

=SUM(IF(ISNUMBER(MATCH(A2:A8,B2:B6,0)),1))

2. Just enter:

=SUMPRODUCT((ISNUMBER(MATCH(A2:A8,B2:B6,0))+0)

3. Just enter:

=SUMPRODUCT(COUNTIFS(A2:A8,B2:B6))

The last one is the least efficient.

 

[Sourav Das]

Thanks a million Aladin bro. This works just fine.

I would request another additional feature to this. If the value is Greater than or equal to 22 or Cell no. B4, how would I count that in addition to the factors already taken under consideration.

1. control+shift+enter, not just enter:

=SUM(IF(ISNUMBER(MATCH(A2:A8,B2:B6,0)),1))

2. Just enter:

=SUMPRODUCT((ISNUMBER(MATCH(A2:A8,B2:B6,0))+0)

3. Just enter:

=SUMPRODUCT(COUNTIFS(A2:A8,B2:B6))

The last one is the least efficient.

 

[Aladin Akyurek]

Thanks a million Aladin bro. This works just fine.

I would request another additional feature to this. If the value is Greater than or equal to 22 or Cell no. B4, how would I count that in addition to the factors already taken under consideration.

Do you mean...

=SUM(IF(ISNUMBER(MATCH(IF(A2:A8>=22,A2:A8),B2:B6,0)),1))

which must be confirmed with control+shift+enter, not just enter?