# Count Unique Value combinations with Conditions

#### jrc1115

##### New Member
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)

 Active Customer Name Project Active Count N Customer 1 A N Customer 1 A Y Customer 1 B Y Customer 1 B Y Customer 1 B Y Customer 1 C Y Customer 1 C N Customer 2 AA N Customer 2 AA N Customer 2 AA Y Customer 2 BB Y Customer 2 BB Y Customer 2 BB

<tbody>
</tbody>

Thanks a lot for the help!!

Josh

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

#### perimidt

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

#### markmzz

##### MrExcel MVP
Try this:

Code:
In D2

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

Markmzz

Last edited:

#### vogel997

##### Active Member
One more...

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

<colgroup><col></colgroup><tbody>
</tbody>

#### markmzz

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

Layout

 Active Customer Name Project Active Count N Customer 1 A 0 N Customer 1 A 0 Y Customer 1 B 1 Y Customer 1 B 0 Y Customer 1 B 0 Y Customer 1 C 1 Y Customer 1 C 0 N Customer 2 AA 0 N Customer 2 AA 0 N Customer 2 AA 0 Y Customer 2 BB 1 Y Customer 2 BB 0 Y Customer 2 BB 0 ******* *************** ******** *************

<tbody>
</tbody>

Formula

Code:
In D2

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

Markmzz

#### markmzz

##### MrExcel MVP
A small modification in my last formula:

Code:
In D2

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

Markmzz

#### jrc1115

##### New Member
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

##### New Member
Thanks Mark! This worked as well! Appreciate everyones quick replies!!

Josh

#### markmzz

##### MrExcel MVP
Thanks Mark! This worked as well! Appreciate everyones quick replies!!

Josh

You are welcome and thanks for the feedback.

Markmzz

Replies
7
Views
90
Replies
14
Views
385
Replies
9
Views
142
Replies
3
Views
106
Replies
10
Views
146

1,195,967
Messages
6,012,600
Members
441,715
Latest member
TTP

### 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.

### Which adblocker are you using?

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

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