Need to calculate status of each owner?

    Re: Need to calculate status of each owner?


    [sw]*[/sw]

    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

    Re: Need to calculate status of each owner?


    OK - we are halfway there!


    Please manually add a few rows of excpected outcomes to your workbook and upload again - you will need to explain the logic for each of these. It is not immediately obvious to me what you are after here.

    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

    Re: Need to calculate status of each owner?


    The spreadsheet still hasn't been updated as requested ...

    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

    Re: Need to calculate status of each owner?


    Hello,


    I uploaded the spreadsheet again, I highlight the columns which I think there should be a formula to get the count of "Total Complete WO Per Owner" & "Total Open WO Per Owner"


    I know I can do it manually using the filter of WO Status, But it is stressful is there a better way to do it than manual ?


    I hopy my explanation and spreadsheet is clear enough :)

    Re: Need to calculate status of each owner?


    Try this for starters in I2 copied down:


    =COUNTIFS(E:E,"COMP",K:K,K2)


    You can adapt this for other criteria, e.g. for anything that isn't COMP:


    =COUNTIFS(E:E,"<>COMP",K:K,K2)

    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

    Re: Need to calculate status of each owner?


    If you only want each owner's stats to appear once only on their first row, use this in I2:


    =IF(COUNTIF(K$2:K2,K2)>1,"",COUNTIFS(E:E,"COMP",K:K,K2))


    and this in J2:


    =IF(COUNTIF(K$2:K2,K2)>1,"",COUNTIFS(E:E,"<>COMP",K:K,K2))

    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

    Re: Need to calculate status of each owner?


    Any good? An acknowledgement one way or the other would be appreciated.

    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

    Re: Need to calculate status of each owner?


    AliGW
    I couldn't reply due to I have work and there was no time, Sorry.
    I will try to check the code ASAP and give you feedback regarding output.


    Re: Need to calculate status of each owner?


    Thanks for letting me know.

    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

    Re: Need to calculate status of each owner?


    AliGW


    Code
    =COUNTIFS(E:E,"COMP",K:K,K2)


    I try above it work but calculate not correctly, If i do manual it will show wrong count.


    Code
    =IF(COUNTIF(K$2:K2,K2)>1,"",COUNTIFS(E:E,"COMP",K:K,K2))


    It worked Ok.


    Code
    =IF(COUNTIF(K$2:K2,K2)>1,"",COUNTIFS(E:E,"<>COMP",K:K,K2))


    It didn't work, It should calculate all values except for COMP


    What I did is used Pivot Table and I manipluate the sheet.

    Re: Need to calculate status of each owner?


    Try these


    In I2 and copied down


    =COUNTIFS(H:H,H2,E:E,"COMP")


    In J2 and copied down


    =COUNTIFS(H:H,H2,E:E,"*APPR")

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Re: Need to calculate status of each owner?


    Xani - all of the formulae I provided work on the samplke datasheet you posted here.

    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

    Re: Need to calculate status of each owner?


    ali


    I have done some other work for this OP, his table headers are very confusing!


    I think you will find that by "Owner" he actual means "Project Manager" if he changes your formulae to reference column H rather than column K I think he will get the expected result.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Re: Need to calculate status of each owner?


    Thanks for this. I think he needs to adapt my formulae to suit what he really means/wants, not the other way round!!! ;)

    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

    Re: Need to calculate status of each owner?


    Agree, I edited my last reply! :)

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

Participate now!

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

Register Yourself Login