Count values in a column if it match the adjacent cell

[somydelafomy]

Hello,

I am building a sheet to keep track of various standards' revisions of the our industry. I would like to display the quantity of outdated standards in a welcome page but I can't get my formulas to work. I want the formula to count a cell if it matches the adjacent cell. I have tried variation of the following formula but I can't get it to work: =COUNTIF(Revision!A2:A45, "="Revision!B2:B45)

The result that it should return is 7.

Owned Revision	Revision Available
9	9
1(2013)	1(2013)
12	14
10	12
9	13
9	12
11	13
11B	13D
8	14
11	11

<tbody>
</tbody>

Thanks in advance for your assistance. Sorry if If made some English mistakes as it's not my native language and I'm still learning.

 

[shahfahad]

Ctrl + shift + enter

=countif(a2:a11,b2:b11)

 

[Andrew Poulsom]

Welcome to MrExcel.

Does this work for you?

=SUMPRODUCT(--(A2:A45=B2:B45))

 

[somydelafomy]

Ctrl + shift + enter

=countif(a2:a11,b2:b11)

Thanks for the quick reply. It's greatly appreciated. With that formula and the CTRL+Shift+Enter the result I get is '3' instead of '7'.

 

[somydelafomy]

Welcome to MrExcel.

Does this work for you?

=SUMPRODUCT(--(A2:A45=B2:B45))

Hi Andrew. Thanks for the welcome. I have tried your formula and the result that I get is '37' instead of '7'.

 

[Weazel]

maybe I'm mistaken but wouldn't you want to count revisions that are not equal ?


=SUMPRODUCT(--(A2:A11<>B2:B11))

 

[Andrew Poulsom]

maybe I'm mistaken but wouldn't you want to count revisions that are not equal ?


=SUMPRODUCT(--(A2:A11<>B2:B11))

I wondered that but the OP used = in the formula he tried.

 

[somydelafomy]

maybe I'm mistaken but wouldn't you want to count revisions that are not equal ?


=SUMPRODUCT(--(A2:A11<>B2:B11))

It worked! Thank you so much! Been scratching my head for a while over that one... Yeah I wanted to count the revision that are not equal. I've inserted the '=' sign by mistake when I wrote it on this post. Sorry for any confusion this may have caused.

Again thanks a lot to you both! It's really appreciated.
Best regards,
-Tom