count unique based on multiple criteria

[PivotMeThis]

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.

 

[PivotMeThis]

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

 

[Aladin Akyurek]

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]

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?

 

[Aladin Akyurek]

Do you mean...

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

perhaps?

 

[PivotMeThis]

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.

 

[Aladin Akyurek]

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]

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]

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

 

[PivotMeThis]

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?