Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 18th 19, 03:21 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2014
Posts: 6
Default 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!

  #2   Report Post  
Old December 18th 19, 03:34 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,762
Default 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
  #3   Report Post  
Old December 18th 19, 03:51 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2014
Posts: 6
Default 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?
  #4   Report Post  
Old December 18th 19, 04:00 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,762
Default 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
  #5   Report Post  
Old December 18th 19, 04:24 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2014
Posts: 6
Default 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!


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA: Matching cells across columns, and returning equal values Babymech Excel Discussion (Misc queries) 0 January 26th 09 05:41 PM
Count occurances of equal values in two col. - array within SUMPRO John_J Excel Worksheet Functions 5 September 30th 08 02:19 PM
Determining if two columns values are equal/close GDCross Excel Discussion (Misc queries) 4 February 6th 07 01:44 PM
Count number of values equal to MAX of a column dauclair Excel Discussion (Misc queries) 1 May 30th 06 02:23 PM
If values in two columns are equal I want to automatically execute a macro Tom Rinks Excel Programming 1 February 1st 04 05:58 PM


All times are GMT +1. The time now is 10:21 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017