Rank Occurrences of text with If condition

  • I'm working on a data set as displayed below.


    I need to Index the maintenance and breakdown schedule of few equipment. The screenshot below displays the data for 2 equipment (Column G) with their maintenance schedule.


    Column A & B lists the Start date and time of maintenance/breakdown, Column C & D lists the completion date and time of Maintenance/Breakdown. I need to fill in the data in last 2 columns for Index of Maintenance & Breakdown for 2 equipment LPDT 65 and 66 (Column G).


    The data in Column N and O are filled as per below logic. For each Equip # (Column G), If Column M is "PM" and Column E is "Not repeated", Increase Maintenance # by 1.
    Similarly for Column O, For each Equip # (Column G), If Column M is "BD" and Column E is "Not repeated", Increase Breakdown # by 1.


    I've attached the file for reference. Please find below a screenshot of data.
    [ATTACH=JSON]{"data-align":"none","data-size":"large","data-attachmentid":1193727}[/ATTACH] I've also asked this question at: https://www.mrexcel.com/forum/excel-...onditions.html

  • Hello,


    My guess is you would be getting a great deal of answers .... if you were to attach your file ... :wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Carim - could you please outline the solution offered in the body of the post as well? Thanks.

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • Hello Ali,


    Formula proposed in workbook is :


    Code
    =SUMPRODUCT(($G$2:G2=$G$2)*($M$2:M2="PM")*($E$2:E2="Not Repeated"))


    P:S:


    In you Signature, you do mention the 'reputation star' bottom left ... where is it exactly ... ???

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • It has gone sonce the forum upgrade - I'd better change my sig line! Thanks. :)

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!