Count Vaules in a range that appear in another range
 

I have three ranges e.g.

Range 1 Range 2 Range 3
Bread Apples Bread
Apples Oranges Buns
Oranges Pears
Buns
Bread
Pears
Apples

I'm trying to construct a formula that counts the number of times, say, and
entry in Range 3 appears in Range 1 - In this instance the answer would be 3.

Count Vaules in a range that appear in another range
 

Hi,
try

=SUMPRODUCT(($A$2:$A$100=c2)+($A$2:$A$100=c3))

Being a2 to a100 range 1 and c2 and c3 products in range 3

"T Newbery" wrote:


I have three ranges e.g.

Range 1 Range 2 Range 3
Bread Apples Bread
Apples Oranges Buns
Oranges Pears
Buns
Bread
Pears
Apples

I'm trying to construct a formula that counts the number of times, say, and
entry in Range 3 appears in Range 1 - In this instance the answer would be 3.

Count Vaules in a range that appear in another range
 

Thanks, but is there a way to do this without enumerating everything in range
3. It's basically a lookup list that changes over time. I only want to change
the list, not everything that references it...

"Eduardo" wrote:

Hi,
try

=SUMPRODUCT(($A$2:$A$100=c2)+($A$2:$A$100=c3))

Being a2 to a100 range 1 and c2 and c3 products in range 3

"T Newbery" wrote:


I have three ranges e.g.

Range 1 Range 2 Range 3
Bread Apples Bread
Apples Oranges Buns
Oranges Pears
Buns
Bread
Pears
Apples

I'm trying to construct a formula that counts the number of times, say, and
entry in Range 3 appears in Range 1 - In this instance the answer would be 3.

Count Vaules in a range that appear in another range
 

=SUMPRODUCT(COUNTIF(A1:A100,C1:C100))


"T Newbery" wrote:

Thanks, but is there a way to do this without enumerating everything in range
3. It's basically a lookup list that changes over time. I only want to change
the list, not everything that references it...

"Eduardo" wrote:

Hi,
try

=SUMPRODUCT(($A$2:$A$100=c2)+($A$2:$A$100=c3))

Being a2 to a100 range 1 and c2 and c3 products in range 3

"T Newbery" wrote:


I have three ranges e.g.

Range 1 Range 2 Range 3
Bread Apples Bread
Apples Oranges Buns
Oranges Pears
Buns
Bread
Pears
Apples

I'm trying to construct a formula that counts the number of times, say, and
entry in Range 3 appears in Range 1 - In this instance the answer would be 3.

Count Vaules in a range that appear in another range
 

Hi,

I assume your range to be counted is in column B

=SUMPRODUCT(COUNTIF(A1:A100,B1:B100))

"T Newbery" wrote:

Thanks, but is there a way to do this without enumerating everything in range
3. It's basically a lookup list that changes over time. I only want to change
the list, not everything that references it...

"Eduardo" wrote:

Hi,
try

=SUMPRODUCT(($A$2:$A$100=c2)+($A$2:$A$100=c3))

Being a2 to a100 range 1 and c2 and c3 products in range 3

"T Newbery" wrote:


I have three ranges e.g.

Range 1 Range 2 Range 3
Bread Apples Bread
Apples Oranges Buns
Oranges Pears
Buns
Bread
Pears
Apples

I'm trying to construct a formula that counts the number of times, say, and
entry in Range 3 appears in Range 1 - In this instance the answer would be 3.

Count Vaules in a range that appear in another range
 

Thanks, that's exactly what I need.

I'm not even going to pretend I know why that works when COUNTIF by itself
doesn't...

"Teethless mama" wrote:

=SUMPRODUCT(COUNTIF(A1:A100,C1:C100))


"T Newbery" wrote:

Thanks, but is there a way to do this without enumerating everything in range
3. It's basically a lookup list that changes over time. I only want to change
the list, not everything that references it...

"Eduardo" wrote:

Hi,
try

=SUMPRODUCT(($A$2:$A$100=c2)+($A$2:$A$100=c3))

Being a2 to a100 range 1 and c2 and c3 products in range 3

"T Newbery" wrote:


I have three ranges e.g.

Range 1 Range 2 Range 3
Bread Apples Bread
Apples Oranges Buns
Oranges Pears
Buns
Bread
Pears
Apples

I'm trying to construct a formula that counts the number of times, say, and
entry in Range 3 appears in Range 1 - In this instance the answer would be 3.