Count duplicates across 3 columns, ignore unique (apply to entire column)

[KariSaga]

Hello,

I am looking for some help with a way to create a formula that looks at three columns if there are duplicates that are common across all three columns to count them, but if there are no duplicates found I do not want a count. If possible I would like to do this for the entire column without dragging the formula down as there are over 10k rows. Below is an example and formula in Column E that I have now.

=COUNTIFS(B:B,B2,C:C,C2,D:D,D2)

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Column1[/TD]
[TD]Column2[/TD]
[TD]Column3[/TD]
[TD]Column4[/TD]
[TD]Count[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]asdf[/TD]
[TD]PC208[/TD]
[TD]saw-fra[/TD]
[TD]1/1/2019[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]asdf[/TD]
[TD]PC255[/TD]
[TD]dfw-kkd[/TD]
[TD]1/5/2018[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]aasdgd[/TD]
[TD]PC011[/TD]
[TD]jgg-lwc[/TD]
[TD]7/5/2014[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]asdfad[/TD]
[TD]PC208[/TD]
[TD]saw-fra[/TD]
[TD]1/1/2019[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]asdfad[/TD]
[TD]PC777[/TD]
[TD]omt-lis[/TD]
[TD]7/14/2018[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]asdf[/TD]
[TD]PC208[/TD]
[TD]ohc-una[/TD]
[TD]1/1/2019[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]asdfad[/TD]
[TD]PC777[/TD]
[TD]omt-lis[/TD]
[TD]7/14/2018[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]asdf[/TD]
[TD]PC208[/TD]
[TD]saw-fra[/TD]
[TD]1/1/2019[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

This is what I am expecting as a result.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Column1[/TD]
[TD]Column2[/TD]
[TD]Column3[/TD]
[TD]Column4[/TD]
[TD]Count[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]asdf[/TD]
[TD]PC208[/TD]
[TD]saw-fra[/TD]
[TD]1/1/2019[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]asdf[/TD]
[TD]PC255[/TD]
[TD]dfw-kkd[/TD]
[TD]1/5/2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]aasdgd[/TD]
[TD]PC011[/TD]
[TD]jgg-lwc[/TD]
[TD]7/5/2014[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]asdfad[/TD]
[TD]PC208[/TD]
[TD]saw-fra[/TD]
[TD]1/1/2019[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]asdfad[/TD]
[TD]PC777[/TD]
[TD]omt-lis[/TD]
[TD]7/14/2018[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]asdf[/TD]
[TD]PC208[/TD]
[TD]ohc-una[/TD]
[TD]1/1/2019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]asdfad[/TD]
[TD]PC777[/TD]
[TD]omt-lis[/TD]
[TD]7/14/2018[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]asdf[/TD]
[TD]PC208[/TD]
[TD]saw-fra[/TD]
[TD]1/1/2019[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

Well, you could use this:

=IF(COUNTIFS(B:B,B2,C:C,C2,D:D,D2)=1,"",COUNTIFS(B:B,B2,C:C,C2,D:D,D2))

Though I do not know how slow that is going to be for 10k rows.

Another option is to continue to use the formula as you have it, and use either Conditional Formatting or Custom Formatting to hide the 1's (in Conditional Formatting, just check for a value of 1 and make the color of the text white, the same as the background, so it blends in any you cannot see it).

Note that you do NOT need to drag the formula down for 10k rows. Just put the formula in row 2, and double-click on the bottom right-hand corner of the cell, and it should auto fill all the way down to the bottom of your data.
Any other automated manner would require VBA.

 

[veyselemre]

With VBA;

Sub test()    
    With CreateObject("Scripting.Dictionary")
        son = Cells(Rows.Count, 1).End(3).Row
        For i = 2 To son
            ky = Cells(i, 2).Value & "|" & Cells(i, 3).Value & "|" & Cells(i, 2).Value & "|"
            .Item(ky) = .Item(ky) + 1
        Next i
        For i = 2 To son
            ky = Cells(i, 2).Value & "|" & Cells(i, 3).Value & "|" & Cells(i, 2).Value & "|"
            If .Item(ky) > 1 Then Cells(i, "F").Value = .Item(ky)
        Next i
    End With
End Sub