Getting COUNTIFS to recognise blank/empty cells

dozen

New Member
Joined
Apr 5, 2018
Messages
2
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.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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","")
 
Upvote 0

Forum statistics

Threads
1,226,063
Messages
6,188,653
Members
453,489
Latest member
jessrw

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top