+ Reply to Thread
Results 1 to 3 of 3

Countifs Until Criteria Is Match

  1. #1
    Registered User
    Join Date
    01-20-2010
    Location
    None of your Business
    MS-Off Ver
    None
    Posts
    26

    Countifs Until Criteria Is Match

    I am looking for some help, I have been searching the forums all morning but have failed to come up with a solution so I am looking to the members expertise for help!

    Here is a basic setup of my table.

    Column A - Dates
    Column B - Type (Flat, AW or NH)
    Column C - Runner (Horse Names)
    Column D - Result (Won, Place or Lost)

    Now this is what I am trying to achieve, I want to count the number of rows UNTIL the results column produces "Won" but I have multiple if functions.

    The Countifs formula would be =Countifs([Column A],"<"&[@[Column A]],[Column B],[@[Column B]],[@[Column C],[Column C])

    This obviously counts the whole workbook, I want it to count only up to the first instance of 'Won' in Column D and the have it reset for the next row and count until the next 'Won' again and so on until the end of the workbook.

    For example

    20/02/2013 AW Abigails Angel Lost - Count would be 2
    11/01/2013 AW Abigails Angel Placed - Count would be 1
    27/12/2012 AW Abigails Angel Won - Count would be 5
    24/10/2012 AW Abigails Angel Placed - Count would be 4
    25/08/2012 AW Abigails Angel Lost - Count would be 3
    12/08/2012 AW Abigails Angel Lost - Count would be 2
    19/07/2012 AW Abigails Angel Placed - Count would be 1
    11/06/2012 AW Abigails Angel Won

    Hope I have explained it ok and if not let me know otherwise thank you in advance for any advice/help.

  2. #2
    Forum Contributor
    Join Date
    02-28-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    272

    Re: Countifs Until Criteria Is Match

    There u go....
    Attached Files Attached Files
    Please consider adding a * if I helped

  3. #3
    Registered User
    Join Date
    01-20-2010
    Location
    None of your Business
    MS-Off Ver
    None
    Posts
    26

    Re: Countifs Until Criteria Is Match

    Thank you for the effort but not quite what I am looking for, I guess I should have given a better example of the table. Columns B and C are not all of the same so it would be more like this.....

    20/02/2013 AW Abigails Angel Lost - Count would be 2
    11/01/2013 AW Abigails Angel Placed - Count would be 1
    27/12/2012 AW Abigails Angel Won - Count would be 3
    24/10/2012 AW Abigails Angel Placed - Count would be 2
    25/08/2012 FLAT Abigails Angel Lost - Count would be 2
    12/08/2012 FLAT Abigails Angel Lost - Count would be 1
    19/07/2012 AW Abigails Angel Placed - Count would be 1
    11/06/2012 AW Abigails Angel Won - Count would be Blank as no Previous Win
    10/06/2012 FLAT Abigails Angel Won - Count would be Blank as no Previous Win
    20/02/2013 AW Bobs Delight Lost - Count would be 5
    11/01/2013 AW Bobs Delight Placed - Count would be 4
    27/12/2012 FLAT Bobs Delight Lost - Count would be 1
    24/10/2012 AW Bobs Delight Placed - Count would be 3
    25/08/2012 AW Bobs Delight Lost - Count would be 2
    12/08/2012 FLAT Bobs Delight Won - Count would be Blank as no Previous Win
    19/07/2012 AW Bobs Delight Placed - Count would be 1
    11/06/2012 AW Bobs Delight Won - Count would be Blank as no Previous Win

    So what I need is what you have given me but with multiple conditions inserted into it. Is this possible?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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