Getting COUNTIFS to recognise blank/empty cells

[dozen]

I have a worksheet that contains data setup similar to the data below.

Basically im trying to recognise rows that are not distinct based on the values in 2 columns.

So row 2 , column e has the formula:
=IF(COUNTIFS(A:A,A2,C:C,C2)>1,"Duplicate","")

and so on for the remaining rows.

rows 8 and 9 give the correct desired result, but the formula isnt recognising rows 4 and 5 due to Column D being empty rather than containing values.

[TABLE="width: 478"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 478"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]CODE1VALUE[/TD]
[TD]CODE1NAME[/TD]
[TD]CODE2VALUE[/TD]
[TD]CODE2NAME[/TD]
[TD]Duplicate?[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]red[/TD]
[TD]1[/TD]
[TD]small [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]red[/TD]
[TD]2[/TD]
[TD]big[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]blue[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]blue[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]blue[/TD]
[TD]2[/TD]
[TD]big[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]green[/TD]
[TD]1[/TD]
[TD]small [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]orange[/TD]
[TD]1[/TD]
[TD]small [/TD]
[TD]Duplicate[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]orange[/TD]
[TD]1[/TD]
[TD]small [/TD]
[TD]Duplicate[/TD]
[/TR]
</tbody>[/TABLE]

trying to put some data integrity checks on a manual integration process that should not exist, but it's not going anywhere and im stuck with it.

 

[Legacy 143009]

I have a worksheet that contains data setup similar to the data below.

Basically im trying to recognise rows that are not distinct based on the values in 2 columns.

So row 2 , column e has the formula:
=IF(COUNTIFS(A:A,A2,C:C,C2)>1,"Duplicate","")

and so on for the remaining rows.

rows 8 and 9 give the correct desired result, but the formula isnt recognising rows 4 and 5 due to Column D being empty rather than containing values.

[TABLE="width: 478"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 478"]
<tbody>[TR]
[TD]CODE1VALUE[/TD]
[TD]CODE1NAME[/TD]
[TD]CODE2VALUE[/TD]
[TD]CODE2NAME[/TD]
[TD]Duplicate?[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]red[/TD]
[TD]1[/TD]
[TD]small[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]red[/TD]
[TD]2[/TD]
[TD]big[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]blue[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]blue[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]blue[/TD]
[TD]2[/TD]
[TD]big[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]green[/TD]
[TD]1[/TD]
[TD]small[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]orange[/TD]
[TD]1[/TD]
[TD]small[/TD]
[TD]Duplicate[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]orange[/TD]
[TD]1[/TD]
[TD]small[/TD]
[TD]Duplicate[/TD]
[/TR]
</tbody>[/TABLE]

trying to put some data integrity checks on a manual integration process that should not exist, but it's not going anywhere and im stuck with it.

Try;

=IF(COUNTIFS(A:A,A2,C:C,C2,C:C,"<>"&"")>1,"Duplicate","")

 

[Marcelo Branco]

Maybe

=IF(COUNTIFS(A:A,A2,C:C,C2&"")>1,"Duplicate","")

M.

 

[dozen]

Maybe

=IF(COUNTIFS(A:A,A2,C:C,C2&"")>1,"Duplicate","")

M.

Hi Flashbond i couldnt get yours to work, but Marcelo Branco

=IF(COUNTIFS(A:A,A2,C:C,C2&"")>1,"Duplicate","")

Works for me!!!

Thanks so much

 

[Marcelo Branco]

You are welcome. Glad to help.

M.