# count unique based on multiple criteria

#### PivotMeThis

##### Active Member
I cannot find what I am looking for and it seems like I have done this before but just can't remember.

I have two columns that contain duplicates. One column (K) contains Project Numbers and the other column (X) contains Item Numbers. I need a formula that will return a 1 or a 0.

If the project number and the item number are listed together once, then the formula should return a 1 but if the project number and the item number are listed together more than once, the other entries should show a 0.

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

#### PivotMeThis

##### Active Member
I figured it out...=IF(SUMPRODUCT((\$K\$2:\$K2=K2)*(\$X\$2:\$X2=X2))>1,0,1)

##### MrExcel MVP
I figured it out...=IF(SUMPRODUCT((\$K\$2:\$K2=K2)*(\$X\$2:\$X2=X2))>1,0,1)

If you must invoke SumProduct, shorter

=1-(SUMPRODUCT((\$K\$2:\$K2=K2)*(\$X\$2:\$X2=X2))>1)

would suffice.

The following would be faster:

=1-(COUNTIFS(\$K\$2:\$K2,K2,\$X\$2:\$X2,X2)>1)

#### PivotMeThis

##### Active Member
If you must invoke SumProduct, shorter

=1-(SUMPRODUCT((\$K\$2:\$K2=K2)*(\$X\$2:\$X2=X2))>1)

would suffice.

The following would be faster:

=1-(COUNTIFS(\$K\$2:\$K2,K2,\$X\$2:\$X2,X2)>1)

Thank you, I appreciate your help. Now I am trying to add a third item for another field. Column F contains a date which is sometimes blank. It is the same, if the project number, item number and date are all repeated it should only be counted once. I tried using your count if and adding the new criteria but it counts it if the date is blank.

=1-(COUNTIFS(\$K\$2:\$K36,K36,\$X\$2:\$X36,X36,\$F\$2:\$F36,F36)>1)

Is there a way to do that?

##### MrExcel MVP
Do you mean...

=1-(COUNTIFS(\$K\$2:\$K2,K2,\$K\$2:\$K2,"<>",\$X\$2:\$X2,X2,\$F\$2:\$F2,F2)>1)

perhaps?

#### PivotMeThis

##### Active Member
Do you mean...

=1-(COUNTIFS(\$K\$2:\$K2,K2,\$K\$2:\$K2,"<>",\$X\$2:\$X2,X2,\$F\$2:\$F2,F2)>1)

perhaps?

That still counts the blank fields in column F. I tried changing it up a bit and made a real mess of things.

##### MrExcel MVP
That still counts the blank fields in column F. I tried changing it up a bit and made a real mess of things.

Sorry, I meant to do it for F...

=1-(COUNTIFS(\$K\$2:\$K2,K2,,\$X\$2:\$X2,X2,\$F\$2:\$F2,F2,\$F\$2:\$F2,"<>")>1)

#### PivotMeThis

##### Active Member
Sorry, I meant to do it for F...

=1-(COUNTIFS(\$K\$2:\$K2,K2,,\$X\$2:\$X2,X2,\$F\$2:\$F2,F2,\$F\$2:\$F2,"<>")>1)

Now it says "You've entered too few arguments for this function".

#### PivotMeThis

##### Active Member
I found an extra comma after the last K2 and removed it. It's still counting blanks in row f.

#### PivotMeThis

##### Active Member
This is probably going to sound stupid, because I'm not sure if I'm reading the formula correctly - but might the problem be 1-nothing is still 1?

Replies
2
Views
296
Replies
0
Views
131
Replies
12
Views
321
Replies
6
Views
180
Replies
1
Views
149

1,190,657
Messages
5,982,137
Members
439,757
Latest member
85Sarah2005

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