simple countif question

[bolo]

Hi All,

I am trying to get a countif array formula to work.

The formula i am try to get looks like
{=countif($A$1:$A$10,B$1:B$10)}

So basically in column b i will have a number of values, and i am basically trying to see if any of the values in column b is found in column a.

So the above formula would return a positive number if a value in b is found in a.

HOwever the formula only seems to work if the matching number is in the top row.

eg
1 ,3
2, 2
4
5
6
7
8
9
10
11

doesn't give the required result.
how can i get this countif array formula to work?

Thanks

Bolo

 

[Aladin Akyurek]

Hi All,

I am trying to get a countif array formula to work.

The formula i am try to get looks like
{=countif($A$1:$A$10,B$1:B$10)}

So basically in column b i will have a number of values, and i am basically trying to see if any of the values in column b is found in column a.

So the above formula would return a positive number if a value in b is found in a.

HOwever the formula only seems to work if the matching number is in the top row.

eg
1 ,3
2, 2
4
5
6
7
8
9
10
11

doesn't give the required result.
how can i get this countif array formula to work?

Thanks

Bolo

=SUMPRODUCT(COUNTIF($A$1:$A$10,B$1:B$10))

counts the occurences of B1:B10 in A1:A10 at once and total them.

If you want to count the occurrences of the items from B1:B10 one by one...

In C1 enter & copy down:

=COUNTIF($A$1:$A$10,B$1)

 

[bolo]

=SUMPRODUCT(COUNTIF($A$1:$A$10,B$1:B$10))

counts the occurences of B1:B10 in A1:A10 at once and total them.

If you want to count the occurrences of the items from B1:B10 one by one...

In C1 enter & copy down:

=COUNTIF($A$1:$A$10,B$1)

thanks didnt think about using the sumproduct to encompass the whole formula, i thought the array formula wold be sufficient.

regads

Bolo

 

[Aladin Akyurek]

thanks didnt think about using the sumproduct to encompass the whole formula, i thought the array formula wold be sufficient.

regads

Bolo

You are welcome.

If you replace SumProduct with Sum and confirm the formula with control+shift+enter, again the same effect will obtain.

 

[T. Valko]

Hi All,

I am trying to get a countif array formula to work.

The formula i am try to get looks like
{=countif($A$1:$A$10,B$1:B$10)}

So basically in column b i will have a number of values, and i am basically trying to see if any of the values in column b is found in column a.

So the above formula would return a positive number if a value in b is found in a.

HOwever the formula only seems to work if the matching number is in the top row.

eg
1 ,3
2, 2
4
5
6
7
8
9
10
11

doesn't give the required result.
how can i get this countif array formula to work?

Thanks

Bolo

Here's another one...

Book1
	A	B
1	2	1
2	3	2
3	4	3
4	6	4
5	6	7
6	6
7	7
8	8
9	8
10	10
Sheet1

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A10,B1:B5,0))))