1. Forum
  2. Usenet
  3. MSOFT.PUB.EXCEL.WORKSHEET

  • An easier way to count equal values in two columns?

    From Claus Busch@21:1/5 to All on Wed Dec 18 15:34:53 2019
    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Emilio Guerra@21:1/5 to All on Wed Dec 18 06:21:41 2019
    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!

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Emilio Guerra@21:1/5 to Claus Busch on Wed Dec 18 06:51:46 2019
    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?

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Claus Busch@21:1/5 to All on Wed Dec 18 16:00:49 2019
    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Emilio Guerra@21:1/5 to Claus Busch on Wed Dec 18 07:24:25 2019
    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!

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)