# Need to calculate status of each owner?

• 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"

## Images

• 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.

• 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.

## Files

• 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.

• 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.

• 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

## Files

• 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.

• 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.

• 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.

• 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.

• 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.

• 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.

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.

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