Join/Match/Count Criteria

John Walden

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?

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

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