How to find unique results based on several conditions

gilsa

New Member
Joined
Sep 16, 2014
Messages
21
Hi folks,
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 SeverityCritical Severity
PPP​
11
YY​
20

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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
I have no Idea about pivot tables, maybe this approach can help you.

INDICE CONINCIDIR multiples criterios.xlsx
ABCDEFGH
1ProjectComponentSeverityComponentHighCritical
2APPPHighPPP21
3BPPPHighYY30
4AYYHigh
5CYYHigh
6APPPCritical
7CYYHigh
8
9
Hoja1
Cell Formulas
RangeFormula
G2:H3G2=COUNT(IF(($B$2:$B$7=$F2)*($C$2:$C$7=G$1)=0,"",1))
 
Upvote 0
Hi Tricepsrators,
Thanks for your reply, but as you can see it doesn't bring the expected results.
I think you didn't take into account the projects.
PPP appears in Project A twice, once as critical and another as High and therefore should be counted as 1 under Critical column.
It also appears in project B as High, so should be counted as 1 under high.

If PPP would appear 4 times under the same project, let's say 2 as high and 2 as Critical, I need to count it only once under Critical. This is the logic.

if you have any other idea, please share.
Thanks!
 
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top