Join/Match/Count Criteria

I have a sheet that is populated by another sheet. And in this populated sheet - I'd like to count how many times certain criteria show up (two specific data points).

I assume I would need to join the two columns (i.e., Last Name and ID) and then if these two items show up together again, I'd like to have a count of these matches.

I've tried, JOIN, COUNTIF, MATCH, but haven't been successful. The highlighted cells would be the result.

Any thoughts?

Find more posts tagged with

Formulas

Accepted answers

Mark Cronk

Hi @John Walden ,

As you suggested, insert a helper column [full ID] with the formula: =[last name]@row+ID@row.

In your Count column us the formula:

=COUNTIF([Full ID]1:[Full ID]@row,@cell=[Full ID]@row)

As you copy the formula down the column make sure it continues to reference [Full ID]1:[full ID]@row.

Work?

Mark

All comments

Mark Cronk

Hi @John Walden ,

As you suggested, insert a helper column [full ID] with the formula: =[last name]@row+ID@row.

In your Count column us the formula:

=COUNTIF([Full ID]1:[Full ID]@row,@cell=[Full ID]@row)

As you copy the formula down the column make sure it continues to reference [Full ID]1:[full ID]@row.

Work?

Mark

John Walden

Hi Mark -

This is great - thanks! I added the absolute value and setup an autofill dynamic and now it works perfectly without copy/pasta. Appreciate it. :)

Mark Cronk

Glad you found a solution. Thank you for contributing to the Community.

Mark

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.