+ Reply to Thread
Results 1 to 8 of 8

Counting matches with previous row

  1. #1
    Registered User
    Join Date
    01-29-2021
    Location
    Canada
    MS-Off Ver
    2019
    Posts
    91

    Counting matches with previous row

    hello,I can count repeated values for each row, repeated that match only previous rows line by line:
    =SUMPRODUCT(COUNTIF($A1:$F1,A2:F2))
    But wondering if I can count them all in a range together?
    Thank you screenshot.2154.jpg
    Attached Files Attached Files
    Last edited by Excl3454; 02-17-2022 at 11:52 PM. Reason: done

  2. #2
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Counting matches with previous row

    Hi,
    the "2" is also repeating in your range and you didnt color this one....
    Is this s a mistake or perhaps I didn't understand your question?

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Counting matches with previous row

    Try, for row 3:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    copied down.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Counting matches with previous row

    If I understand you correct and the answer should be "8", then this formula can help here:
    =SUM(--((FREQUENCY(A2:F6,A2:F6)>1)))
    Attached Files Attached Files

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Counting matches with previous row

    If it's a total of the repeats for each row, the answer is 6.

  6. #6
    Registered User
    Join Date
    01-29-2021
    Location
    Canada
    MS-Off Ver
    2019
    Posts
    91

    Re: Counting matches with previous row

    Quote Originally Posted by belinda200 View Post
    Hi,
    the "2" is also repeating in your range and you didnt color this one....
    Is this s a mistake or perhaps I didn't understand your question?
    sorry I dont mean counting all duplicated,, just counting duplicates that are appears in Only previous row. Thanks

  7. #7
    Registered User
    Join Date
    01-29-2021
    Location
    Canada
    MS-Off Ver
    2019
    Posts
    91

    Re: Counting matches with previous row

    Quote Originally Posted by belinda200 View Post
    If I understand you correct and the answer should be "8", then this formula can help here:
    =SUM(--((FREQUENCY(A2:F6,A2:F6)>1)))
    thanks,,but the answer is 6,I counted for each row Q2:Q6 . question is i just need to count duplicated that are from previous row. thank you

  8. #8
    Registered User
    Join Date
    01-29-2021
    Location
    Canada
    MS-Off Ver
    2019
    Posts
    91

    Re: Counting matches with previous row

    =SUM(COUNTIF(A2:F2,A3:F3))
    counts every row for matches in a row before, so can count them line by line, is possible to count numbers of repeated values in whole range that are repeated only one row above them with one formula?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Summing wildcard matches while subtracting wildcard matches of the previous rows
    By Alzabo in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-03-2020, 05:00 PM
  2. [SOLVED] Counting wildcard matches while subtracting wildcard matches of the previous rows
    By Alzabo in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-24-2020, 02:40 PM
  3. Copy values from previous day, if range matches
    By FallingDown in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 02-22-2012, 09:29 AM
  4. Userform - Check if value in Txt box matches previous entered value
    By CRIMEDOG in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-15-2010, 10:21 AM
  5. counting non matches in two (2) ranges
    By WorkMan in forum Excel General
    Replies: 4
    Last Post: 01-27-2010, 12:16 PM
  6. counting matches
    By kestrel in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-10-2005, 09:06 PM
  7. counting matches
    By Ray Newman via OfficeKB.com in forum Excel General
    Replies: 3
    Last Post: 02-04-2005, 08:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1