Count only first occurrence of criteria

  • Hello


    Id like to produce a count of events that happend after a dealine given the following example table.
    [TABLE="width: 384"]

    [tr]


    [td][/td]


    [/tr]


    [/TABLE]
    [TABLE="class: grid, width: 500"]

    [tr]


    [td]

    Key

    [/td]


    [td]

    Event1

    [/td]


    [td]

    Event2

    [/td]


    [td]

    Event3

    [/td]


    [td]

    Event4

    [/td]


    [td]

    Deadline

    [/td]


    [/tr]


    [tr]


    [td]

    Item1

    [/td]


    [td]

    17:00

    [/td]


    [td]

    20:10

    [/td]


    [td]

    20:30

    [/td]


    [td]

    21:00

    [/td]


    [td]

    20:00

    [/td]


    [/tr]


    [tr]


    [td]

    Item2

    [/td]


    [td]

    17:10

    [/td]


    [td]

    17:30

    [/td]


    [td]

    17:40

    [/td]


    [td]

    20:10

    [/td]


    [td]

    20:00

    [/td]


    [/tr]


    [tr]


    [td]

    Item3

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    20:00

    [/td]


    [/tr]


    [tr]


    [td]

    Item4

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    20:00

    [/td]


    [/tr]


    [tr]


    [td]

    Item5

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    20:00

    [/td]


    [/tr]


    [/TABLE]


    I'd like the following 2 lines above give the following split.


    [TABLE="width: 500"]

    [tr]


    [td]

    Event1

    [/td]


    [td]

    0

    [/td]


    [/tr]


    [tr]


    [td]

    Event2

    [/td]


    [td]

    1

    [/td]


    [/tr]


    [tr]


    [td]

    Event3

    [/td]


    [td]

    0

    [/td]


    [/tr]


    [tr]


    [td]

    Event4

    [/td]


    [td]

    1

    [/td]


    [/tr]


    [/TABLE]


    That is on each row I only want to count the first occurrence of Event time >= Deadline.
    I have been trying to use SUMPRODUCT and managed to count each event that is >= Deadline.
    I'd like to avoid duplicate error counts. If Event1 is late all subsequent events will also be late..etc
    Column named Key are all unique values.


    Does this seem doable to someone?


    Regards,
    Gaius

  • Re: Count only first occurrence of criteria


    Thanks Oeldere - That does indded solve my problems I was kinda stuck in my thinking there. Nice and clean solution. :)