Hi folks,
I am struggling with how to find how many unique values based on several conditions.
I have the following table:
Now, I want to count each component in a given project based on these criteria:
1. If the component has both high and critical severity in the same project, it should be counted as critical only
2. If the component has more than one appearance in the same project, it should be counted once
The expected results in my Pivot table should look like this:
PPP appears 3 times, twice in project A and once in project B. Since in A it has both High and Critical, I should count it once as Critical (he highest severity matters).
YY also appears 3 times, twice in project C and once in project A. Since in project C both appearances are High, I should count it once, and once again as high for its existence in project A.
Any assistance, would be highly appreciated!
Thank you all in advance,
Gil
I am struggling with how to find how many unique values based on several conditions.
I have the following table:
Project | Component | Severity |
A | PPP | High |
B | PPP | High |
A | YY | High |
C | YY | High |
A | PPP | Critical |
C | YY | High |
Now, I want to count each component in a given project based on these criteria:
1. If the component has both high and critical severity in the same project, it should be counted as critical only
2. If the component has more than one appearance in the same project, it should be counted once
The expected results in my Pivot table should look like this:
Component | High Severity | Critical Severity |
PPP | 1 | 1 |
YY | 2 | 0 |
PPP appears 3 times, twice in project A and once in project B. Since in A it has both High and Critical, I should count it once as Critical (he highest severity matters).
YY also appears 3 times, twice in project C and once in project A. Since in project C both appearances are High, I should count it once, and once again as high for its existence in project A.
Any assistance, would be highly appreciated!
Thank you all in advance,
Gil