count if text cell matches other text cell

[adamhaynes17]

Hi...I've been going at this for a while and so I thought I would break down and ask.

I've got one series on a worksheet (worksheet A) that contains text, and I would like to have a total at the bottom of the column that totals the number of cells that match the respective text in another column in a second worksheet. Any thoughts?

apple
pear
lemon
orange
lime
TOTAL MATCHES (3)

<tbody>
</tbody>

worksheet A

apple
watermelon
lemon
orange
cherry

<tbody>
</tbody>

worksheet B

 

[Weazel]

maybe something like...

=SUMPRODUCT(--(A1:A5=Sheet2!A1:A5))

 

[adamhaynes17]

that worked great!!! thank you!! quick question...what effect does the "--" have in that formula? i tried sumproduct before, but the two dashes made all the difference.

 

[adamhaynes17]

any thoughts on not including empty cells?

 

[Weazel]

I'm sure there is a better way to not count the blanks, but you can try...

=SUMPRODUCT((A1:A5=Sheet10!A1:A5)*(NOT(ISBLANK(A1:A5))))


basically the "--" is a unary operator used to convert trues and falses to 1's and 0's.

here's a better explanation Excel SUMPRODUCT Function

 

[Aladin Akyurek]

Hi...I've been going at this for a while and so I thought I would break down and ask.

I've got one series on a worksheet (worksheet A) that contains text, and I would like to have a total at the bottom of the column that totals the number of cells that match the respective text in another column in a second worksheet. Any thoughts?

apple
pear
lemon
orange
lime
TOTAL MATCHES (3)

<tbody>
</tbody>

worksheet A

apple
watermelon
lemon
orange
cherry

<tbody>
</tbody>

worksheet B

any thoughts on not including empty cells?

Try something like...

=SUMPRODUCT(ISNUMBER(MATCH(A2:A5,SheetB!A2:A6,0))+0)

 

[adamhaynes17]

Thank you all so much. It works great!!