Count If combination of two values occurs multiple times
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?
Best Answer
-
@Sam Swain If you are flagging, try =IF(COUNTIFS(Location:Location, Location@row, Count:Count,Count@row)>1,1,0) in the flag column
Answers
-
@Sam Swain If you are flagging, try =IF(COUNTIFS(Location:Location, Location@row, Count:Count,Count@row)>1,1,0) in the flag column
-
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.
-
Awesome, works perfectly thanks Eric!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!