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.

[TABLE="width: 366"]
<tbody>[TR]
[TD]Owned Revision
[/TD]
[TD]Revision Available[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]1(2013)[/TD]
[TD]1(2013)[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]11B[/TD]
[TD]13D[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]11[/TD]
[/TR]
</tbody>[/TABLE]

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