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

 

[Firefly2012]

Hi

If both worksheets are in the same workbook, then you could use:

=SUM(COUNTIF(Sheet2!A1:A5,Sheet1!A1:A5))

Confirmed with Ctrl+Shift+Enter