+ Reply to Thread
Results 1 to 2 of 2

Count occurrences and order according to dates

  1. #1
    Registered User
    Join Date
    10-17-2021
    Location
    Caracas
    MS-Off Ver
    2016
    Posts
    8

    Count occurrences and order according to dates

    Regards. I'm looking for help with this:



    In a variable range (A2:B61), the following data is recorded: date and occurrence; the latter will be one of these 3: "A", "B" or "L"). Search for a formula that returns one of three possible results: AL, BA, or LT

    For the result "AL" to be valid, it must be fulfilled that:

    The sum of "A" is = or > 3.
    The sum of “A” is > “B”.
    At least 4 occurrences of "A", have occurred with a date after the last date of occurrence of "B"

    It is not valid if the sum of "A" < 3, if the sums of "A" and "B" are equal, that there have been no occurrences of "B" once point 3 has been fulfilled.

    For the "BA" result to be valid, the following must be true:

    The sum of "B" is = or > 3.
    The sum of “B” is > “A”.
    At least 4 occurrences of "B", have occurred with a date after the last date of occurrence of "A"

    It is not valid if the sum of "B" < 3, if the sums of "A" and "B" are equal, that there have been no occurrences of "A" once point 3 has been met.

    For all other situations that do not meet the conditions for “AL” and “BA” the result will be “LT”

    I've tried several formulas and I don't get what I'm looking for. I use Excel 2016 and Windows 7. Thanks for the feedback.
    Attached Files Attached Files

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Count occurrences and order according to dates

    Are you calculating the numbers AFTER the last result.
    e.g. the result in C61 counts from A47:A61

    This will probably need helper columns, or maybe a macro.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

+ 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] How to count occurrences between dates on different columns?
    By jhandatx in forum Excel General
    Replies: 3
    Last Post: 05-13-2021, 03:42 PM
  2. Count unique occurrences between two dates and by location
    By MakoTheDog in forum Excel General
    Replies: 5
    Last Post: 10-12-2020, 03:15 PM
  3. [SOLVED] Count occurrences between dates, containing text string.
    By her.rockstar in forum Excel General
    Replies: 10
    Last Post: 05-09-2018, 11:07 AM
  4. Count occurrences between two dates
    By kettlecorn22 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-10-2014, 02:53 PM
  5. Replies: 0
    Last Post: 07-28-2013, 10:24 AM
  6. Count the occurrences of dates in a month against a condition
    By cicilyantony in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-23-2013, 01:26 AM
  7. Count unique occurrences of dates within period
    By areynolds in forum Excel General
    Replies: 6
    Last Post: 10-26-2011, 01:31 PM

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