Count If combination of two values occurs multiple times

Sam Swain

Hello community,

I'm trying to count if or flag when a value repeats in two columns.

As you can see below I have the formula for one column but I want to count if both Location and Count Cells Duplicate.

In this instance I can see that a combination of 'London (location) 3 (count)' occurs twice...

What is the formula for this?

Eric Law

@Sam Swain If you are flagging, try =IF(COUNTIFS(Location:Location, Location@row, Count:Count,Count@row)>1,1,0) in the flag column

Eric Law

@Sam Swain If you are flagging, try =IF(COUNTIFS(Location:Location, Location@row, Count:Count,Count@row)>1,1,0) in the flag column

JCluff

I would add a helper text column that joins Location + Count. you can then use this column to check for duplicate values.

Joined Column Formula: "Location | Count"

=Location@row + "-" + Count@row

Duplicate Column: (checkbox or flag column)

=IF(COUNTIF([Location | Count]:[Location | Count], [Location | Count]@row) > 1, 1)

Use @Eric Law's solution, it uses 1 less column.

Sam Swain

Awesome, works perfectly thanks Eric!