Excel - Count all negative conclusion on rows with duplicated values

[abiblequestion]

Dear Excel Fans,

I can not find the formula or mutiple formulas for the following problem.

I have to following excel sheet:

• I have an excel sheet in which multiple rows has the same number (ID number)
• these ID numbers are split in multiple groups for which we make a conclusion.

I would like to make a summary tab in which I summarize all conclusions per ID number.
But when one ID number has a negative conclusion I only want to count it as negative and not as positive.

I would like to recieve the following output in which a ID number with the conclusion not started is more important than WIP, WIP is more important than NOK and OK is less important than NOK.
[TABLE="width: 601"]
<tbody>[TR]
[TD][/TD]
[TD]NOT STARTED[/TD]
[TD]WIP[/TD]
[TD]NOK[/TD]
[TD]OK[/TD]
[TD]check on Total[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TAB 1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]7[/TD]
[TD="align: right"]15[/TD]
[/TR]
</tbody>[/TABLE]


I have the following data fileds
[TABLE="width: 611"]
<tbody>[TR]
[TD]#[/TD]
[TD]ID number[/TD]
[TD]row data[/TD]
[TD]conclusion[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]56[/TD]
[TD]NOK[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]67[/TD]
[TD]Wip[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[TD]78[/TD]
[TD]OK[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2[/TD]
[TD]89[/TD]
[TD]OK[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]3[/TD]
[TD]100[/TD]
[TD]Wip[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]4[/TD]
[TD]111[/TD]
[TD]Wip[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]5[/TD]
[TD]122[/TD]
[TD]OK[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]6[/TD]
[TD]133[/TD]
[TD]OK[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]7[/TD]
[TD]144[/TD]
[TD]OK[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]7[/TD]
[TD]155[/TD]
[TD]NOK[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]8[/TD]
[TD]166[/TD]
[TD]OK[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]8[/TD]
[TD]177[/TD]
[TD]Wip[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]9[/TD]
[TD]188[/TD]
[TD]NOT STARTED[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]10[/TD]
[TD]199[/TD]
[TD]NOT STARTED[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]10[/TD]
[TD]210[/TD]
[TD]OK[/TD]
[/TR]
</tbody>[/TABLE]

thank you so much for your help.

rgs,

 

[abiblequestion]

I found the answer myself by the following two steps:
1. First a Pivot table
2. a seperate sumif formula for each option.