Hello,
I want to write a formula to enable me calculate the statusof each completed (COMP) or open (APPR+COMP+INPRG+WAPPR) for each owner as sumof COMP or Open
Is that possible, and if how can I do that ? "I hope attached picture is large and can be seen"
Need to calculate status of each owner?



Re: Need to calculate status of each owner?
[sw]*[/sw]

Re: Need to calculate status of each owner?
Dear AliGW,
Thank you for your reply, Please find attached file as you requested.
I hope It will helped. 
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.

Re: Need to calculate status of each owner?
The spreadsheet still hasn't been updated as requested ...


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)

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))

Re: Need to calculate status of each owner?
Any good? An acknowledgement one way or the other would be appreciated.



Re: Need to calculate status of each owner?
Thanks for letting me know.

Re: Need to calculate status of each owner?
AliGW
I try above it work but calculate not correctly, If i do manual it will show wrong count.
It worked Ok.
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")


Re: Need to calculate status of each owner?
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.


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!!!

Re: Need to calculate status of each owner?
Agree, I edited my last reply!
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!