Count number of unique values that meet multiple criteria

[trace_1626]

I have attempted to use the sum/frequency/if formula combinations and have failed (although it could've been user error as I don't frequently use those together).

My data is as follows:

A/ B/ C
786698/ Attempt 1/ Plan A
786698/ Attempt 2/ Plan A
12345/ Attempt 1/ Plan A
12345/ Attempt 2/ Plan A

I am attempting to determine how many unique values in column A meet the unique criteria in columns B and C (e.g. Attempt 1/Plan A, Attempt 2/Plan A). The desired result would equal 2 for each of the variations.

I referenced a previous thread and saw that exact ranges were used vs. columns (e.g. A2:A14 vs A:A), could this be why the sum/frequency/if combination didn't work previously?

 

[Domenic]

Try...

A2:F5

786698	Attempt 1	Plan A		786698	2
786698	Attempt 2	Plan A		12345	2
12345	Attempt 1	Plan A
12345	Attempt 2	Plan A

<tbody>
</tbody>

F2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=SUM(IF(FREQUENCY(IF($A$2:$A$5=E2,MATCH($B$2:$B$5&"#"&$C$2:$C$5,$B$2:$B$5&"#"&$C$2:$C$5,0)),ROW($A$2:$A$5)-ROW($A$2)+1)>0,1))

Hope this helps!

 

[trace_1626]

Thanks, however I realize the example solution I provided led you to this particular solution. I care less about how many times the unique values appeared and more about how many unique values appear based on the scenario of Attempt 1 and Plan A; e.g. how many unique #'s in column A have both Attempt 1 and Plan A?

 

[Aladin Akyurek]

With Domenic's layout, control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($B$2:$B$5="Attempt 1",IF(C$2:$C$5="Plan A",$A$2:$A$5)),$A$2:$A$5),1))

 

[trace_1626]

You are both so awesome and have saved me a TON of time! I knew it had to be something so simple as the placement of the IF statements but I have been wracking my brain!!

Suppose I need to add a 3rd variable, is it as simple as nesting another if? e.g. =SUM(IF(FREQUENCY(IF($B$2:$B$5="Attempt 1",IF($D$2:$D$5="X",IF(C$2:$C$5="Plan A",$A$2:$A$5)),$A$2:$A$5),1)) ?

What if the 3rd variable was an OR vs. AND, how would that need to be written?

 

[Aladin Akyurek]

You are both so awesome and have saved me a TON of time! I knew it had to be something so simple as the placement of the IF statements but I have been wracking my brain!!

Suppose I need to add a 3rd variable, is it as simple as nesting another if? e.g. =SUM(IF(FREQUENCY(IF($B$2:$B$5="Attempt 1",IF($D$2:$D$5="X",IF(C$2:$C$5="Plan A",$A$2:$A$5)),$A$2:$A$5),1)) ?

What if the 3rd variable was an OR vs. AND, how would that need to be written?

Or how?

D2:D5 = X or D2:D5 = Y ...

 

[trace_1626]

Count unique values in A if the meet a new variable criteria in D where D = X or D = Y

 

[Domenic]

To add a new criteria where Column D equals "X" or "Y", try...

=SUM(IF(FREQUENCY(IF($B$2:$B$5="Attempt 1",IF(C$2:$C$5="Plan A",IF(($D$2:$D$5="X")+($D$2:$D$5="Y"),$A$2:$A$5))),$A$2:$A$5),1))

or

=SUM(IF(FREQUENCY(IF($B$2:$B$5="Attempt 1",IF(C$2:$C$5="Plan A",IF(ISNUMBER(MATCH($D$2:$D$5,{"X","Y"},0)),$A$2:$A$5))),$A$2:$A$5),1))

...confirmed with CONTROL+SHIFT+ENTER.

 

[trace_1626]

The second one worked!

I cannot express how grateful I am to both of you and how sad I am I didn't ask for help sooner!! You are both so amazing and have saved me an incredible amount of time and have now allowed me to delegate a much simpler process which adds more time back to my day!

Time and life savers you are!!!