Count Unique Value combinations with Conditions

[jrc1115]

Hello Mr. Excel!

I have a fairly large data set that i'm working with and I am trying to determine the number of unique combinations given a certain criteria. I have 3 fields i'm trying to compare, Active ("Y" or "N"), Customer Name (there are many different names), Project (there are many different projects). There are multiple appearances of "Customer Name" and "Project" from other fields in my data set.

In the 4th column, I'm trying to output a "0" if Active is "N", and a "1" for the first unique combination of Customer Name and Project and "0" for the remaining duplicates. The total dataset is ~4000 rows but this the main problem i'm desperate to solve!

So far, i've tried this which returns a "0" when Active is "N" but I get "#VALUE" when Active is "Y". Any suggestions on how to solve this? This would be copied and pasted in cell D2 and pasted down through the dataset.


=IF(A2="Y",IF(SUMPRODUCT(($B$2*$B2=B2)*($C$2*$C2=C2))>1,0,1),0)

[TABLE="width: 267"]
<tbody>[TR]
[TD]Active
[/TD]
[TD]Customer Name
[/TD]
[TD]Project
[/TD]
[TD]Active Count
[/TD]
[/TR]
[TR]
[TD]N
[/TD]
[TD]Customer 1
[/TD]
[TD]A
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]N
[/TD]
[TD]Customer 1
[/TD]
[TD]A
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Y
[/TD]
[TD]Customer 1
[/TD]
[TD]B
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Y
[/TD]
[TD]Customer 1
[/TD]
[TD]B
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Y
[/TD]
[TD]Customer 1
[/TD]
[TD]B
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Y
[/TD]
[TD]Customer 1
[/TD]
[TD]C
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Y
[/TD]
[TD]Customer 1
[/TD]
[TD]C
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]N
[/TD]
[TD]Customer 2
[/TD]
[TD]AA
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]N
[/TD]
[TD]Customer 2
[/TD]
[TD]AA
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]N
[/TD]
[TD]Customer 2
[/TD]
[TD]AA
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Y
[/TD]
[TD]Customer 2
[/TD]
[TD]BB
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Y
[/TD]
[TD]Customer 2
[/TD]
[TD]BB
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Y
[/TD]
[TD]Customer 2
[/TD]
[TD]BB
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks a lot for the help!!

Josh

 

[perimidt]

Try this formula in D2:
=IF(COUNTIFS(A$2:A2,"Y",B$2:B2,B2,C$2:C2,C2)=1,1,0)

 

[markmzz]

Try this:

In D2

=IF($A2="N",0,--(SUMPRODUCT(-($B$2:$B2=$B2),-($C$2:$C2=$C2),--($A$2:$A2="Y"))=1))

Markmzz

 

[vogel997]

One more...

[TABLE="width: 746"]
<colgroup><col></colgroup><tbody>[TR]
[TD],=IF(A2="N",0,IF(SUMPRODUCT(--(A$2:A2 < > "N"),--(B$2:B2=B2),--(C$2:C2=C2))=1,1,0))[/TD]
[/TR]
</tbody>[/TABLE]

 

[markmzz]

Another way (with a small modification in your formula):

Layout

[TABLE="width: 183"]
<tbody>[TR]
[TD="width: 42, bgcolor: transparent"]Active[/TD]
[TD="width: 82, bgcolor: transparent"]Customer Name[/TD]
[TD="width: 47, bgcolor: transparent"]Project[/TD]
[TD="width: 72, bgcolor: transparent"]Active Count[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]N[/TD]
[TD="bgcolor: transparent"]Customer 1[/TD]
[TD="bgcolor: transparent"]A[/TD]
[TD="bgcolor: yellow, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]N[/TD]
[TD="bgcolor: transparent"]Customer 1[/TD]
[TD="bgcolor: transparent"]A[/TD]
[TD="bgcolor: yellow, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Y[/TD]
[TD="bgcolor: transparent"]Customer 1[/TD]
[TD="bgcolor: transparent"]B[/TD]
[TD="bgcolor: yellow, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Y[/TD]
[TD="bgcolor: transparent"]Customer 1[/TD]
[TD="bgcolor: transparent"]B[/TD]
[TD="bgcolor: yellow, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Y[/TD]
[TD="bgcolor: transparent"]Customer 1[/TD]
[TD="bgcolor: transparent"]B[/TD]
[TD="bgcolor: yellow, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Y[/TD]
[TD="bgcolor: transparent"]Customer 1[/TD]
[TD="bgcolor: transparent"]C[/TD]
[TD="bgcolor: yellow, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Y[/TD]
[TD="bgcolor: transparent"]Customer 1[/TD]
[TD="bgcolor: transparent"]C[/TD]
[TD="bgcolor: yellow, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]N[/TD]
[TD="bgcolor: transparent"]Customer 2[/TD]
[TD="bgcolor: transparent"]AA[/TD]
[TD="bgcolor: yellow, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]N[/TD]
[TD="bgcolor: transparent"]Customer 2[/TD]
[TD="bgcolor: transparent"]AA[/TD]
[TD="bgcolor: yellow, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]N[/TD]
[TD="bgcolor: transparent"]Customer 2[/TD]
[TD="bgcolor: transparent"]AA[/TD]
[TD="bgcolor: yellow, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Y[/TD]
[TD="bgcolor: transparent"]Customer 2[/TD]
[TD="bgcolor: transparent"]BB[/TD]
[TD="bgcolor: yellow, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Y[/TD]
[TD="bgcolor: transparent"]Customer 2[/TD]
[TD="bgcolor: transparent"]BB[/TD]
[TD="bgcolor: yellow, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Y[/TD]
[TD="bgcolor: transparent"]Customer 2[/TD]
[TD="bgcolor: transparent"]BB[/TD]
[TD="bgcolor: yellow, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]*******[/TD]
[TD="bgcolor: transparent"]***************[/TD]
[TD="bgcolor: transparent"]********[/TD]
[TD="bgcolor: transparent"]*************[/TD]
[/TR]
</tbody>[/TABLE]

Formula

In D2

=IF($A2="Y",1*(SUMPRODUCT(-($B$2:$B2=$B2),-($C$2:$C2=$C2))=1),0)

Markmzz

 

[markmzz]

A small modification in my last formula:

In D2

=($A2="Y")*(SUMPRODUCT(-($B$2:$B2=$B2),-($C$2:$C2=$C2),--($A$2:$A2="Y"))=1)

Markmzz

 

[jrc1115]

Try this formula in D2:
=IF(COUNTIFS(A$2:A2,"Y",B$2:B2,B2,C$2:C2,C2)=1,1,0)

Thank you Erik! This works great!

Josh

 

[jrc1115]

Thanks Mark! This worked as well! Appreciate everyones quick replies!!

Josh

 

[markmzz]

Thanks Mark! This worked as well! Appreciate everyones quick replies!!

Josh

You are welcome and thanks for the feedback.

Markmzz