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?

Find more posts tagged with

Formulas

Accepted answers

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

All comments

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.