An easier way to count equal values in two columns?
 

I have a worksheet with two columns, each column has a date/time value. I need to know how many of the cells in column A match the cells in column B on a row-by-row basis. Now, I could do it row by row with IF, but is there a way to look at the whole range and just count in one instance?

To clarify, the same values must only take place in the same row (e.g., A3=B3), and I'd like for the formula to return how many of those cells equal each other. Thanks!

An easier way to count equal values in two columns?
 

Hi Emilio,

Am Wed, 18 Dec 2019 06:21:41 -0800 (PST) schrieb Emilio Guerra:

I have a worksheet with two columns, each column has a date/time value. I need to know how many of the cells in column A match the cells in column B on a row-by-row basis. Now, I could do it row by row with IF, but is there a way to look at the whole range and just count in one instance?

To clarify, the same values must only take place in the same row (e.g., A3=B3), and I'd like for the formula to return how many of those cells equal each other. Thanks!

try:

=SUMPRODUCT(N(A1:A20=B1:B20))


Regards
Claus B.
--
Windows10
Office 2016

An easier way to count equal values in two columns?
 

On Wednesday, December 18, 2019 at 9:34:56 AM UTC-5, Claus Busch wrote:
Hi Emilio,

Am Wed, 18 Dec 2019 06:21:41 -0800 (PST) schrieb Emilio Guerra:

I have a worksheet with two columns, each column has a date/time value. I need to know how many of the cells in column A match the cells in column B on a row-by-row basis. Now, I could do it row by row with IF, but is there a way to look at the whole range and just count in one instance?

To clarify, the same values must only take place in the same row (e.g., A3=B3), and I'd like for the formula to return how many of those cells equal each other. Thanks!

try:

=SUMPRODUCT(N(A1:A20=B1:B20))


Regards
Claus B.
--
Windows10
Office 2016

Thank you so much, Claus. I know I'm pushing my luck and the bounds of courtesy here, but is there a way to make them count the entire column range (eg. A:A and B:B) avoiding blanks?

An easier way to count equal values in two columns?
 

Hi Emilio,

Am Wed, 18 Dec 2019 06:51:46 -0800 (PST) schrieb Emilio Guerra:

Thank you so much, Claus. I know I'm pushing my luck and the bounds of courtesy here, but is there a way to make them count the entire column range (eg. A:A and B:B) avoiding blanks?

try:

=SUMPRODUCT((A1:A20=B1:B20)*(A1:A20<"")*(B1:B20< ""))

Regards
Claus B.
--
Windows10
Office 2016

An easier way to count equal values in two columns?
 

On Wednesday, December 18, 2019 at 10:00:52 AM UTC-5, Claus Busch wrote:
Hi Emilio,

Am Wed, 18 Dec 2019 06:51:46 -0800 (PST) schrieb Emilio Guerra:

Thank you so much, Claus. I know I'm pushing my luck and the bounds of courtesy here, but is there a way to make them count the entire column range (eg. A:A and B:B) avoiding blanks?

try:

=SUMPRODUCT((A1:A20=B1:B20)*(A1:A20<"")*(B1:B20< ""))

Regards
Claus B.
--
Windows10
Office 2016

Thanks!