Fix Interchanged First and Last Names in Running Count in Google Sheets

Published on

This post describes how to fix interchanged first and last names in a running count array formula in Google Sheets.

A regular running count array formula will treat interchanged names as a new entry and return a new count.

For example, if the name “Denise Young” appears twice and “Young Denise” appears thrice, the correct occurrence numbers will be {1, 2, 3, 4, 5}. The incorrect result would be {1, 2, 1, 2, 3}. Please see Figure 1 below.

Fixing interchanged first and last names in running count in Google Sheets
Figure 1

In the above example, I have used an array formula in cell D2 that fixes interchanged first and last names while returning the correct running count.

Note: You must understand one issue that may arise when using such a formula. Even if “Essie Phelps” and “Phelps Essie” are two different names, the formula will treat them as the same. This is because the formula will treat them as swapped names.

That being said, let’s go to the formula and explanation.

How to Fix Interchanged First and Last Names in Running Count in Google Sheets

We will use a LAMBDA formula to return the correct running count after fixing interchanged first and last names.

Here is the formula:

=ARRAYFORMULA(MAP(
     B2:B,C2:C,
     LAMBDA(first,last,IF(first&last="",,SUM(
        COUNTIFS(B2:first&C2:last,{first&last,last&first})
     )))
))

Where:

  • B2:B is the range of cells that contains the first names.
  • C2:C is the range of cells that contains the last names.
  • B2 is the first cell in the range of cells that contains the first names.
  • C2 is the first cell in the range of cells that contains the last names.

The above is an array formula, so you just need to insert it in cell D2. It will expand down. However, if there are any values down the column that prevent the formula from expanding, it will return the #REF! error.

How does the above formula fix the interchanged first and last names while returning the correct running count? Let me explain the formula step-by-step.

Formula Explanation

We will start writing the non-array formula that fixes interchanged names in the running count.

1. Non-Array Formula to Fix Interchanged First and Last Names in Running Count

The use of the OR operator in multiple columns in the COUNTIFS function is the key to the formula that fixes swapped first and last names in the running count of occurrences in Google Sheets.

Here is the formula:

=ARRAYFORMULA(SUM(
     COUNTIFS($B$2:B2&$C$2:C2,{B2&C2,C2&B2})
))

If you insert this formula in cell D2 and drag the fill handle in cell C2 down, you will get the running count after fixing the interchanged first and last names.

Non-array formula that fixes swapped first and last names in running count in Google Sheets.
Figure 2

So, let’s first learn the COUNTIFS formula in detail. Then, we can easily convert it to an array formula that expands.

The syntax of the COUNTIFS function is:

COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...])

The following COUNTIFS formula counts the range $B$2:B2&$C$2:C2 with the criteria {B2&C2,C2&B2}.

=ARRAYFORMULA(COUNTIFS($B$2:B2&$C$2:C2,{B2&C2,C2&B2}))

Where:

  • criteria_range1: $B$2:B2&$C$2:C2
  • criterion1: {B2&C2,C2&B2}

Since the criteria in criterion1 contains two conditions, we have used the ARRAYFORMULA function.

You can read it easily with the help of the following generic formula:

=ARRAYFORMULA(COUNTIFS(first_name&last_name,{first_name&last_name,last_name&first_name}))

The SUM function totals the returned values.

=ARRAYFORMULA(SUM(COUNTIFS($B$2:B2&$C$2:C2,{B2&C2,C2&B2})))

The range becomes $B$2:$B$3&$C$2:$C$3 and the criteria become {B3&C3,C3&B3} in the second row when we drag the formula down. Please see Figure 2 below to understand it.

Swapped names in cumulative count formula explained
Figure 3

2. Array Formula to Fix Swapped First and Last Names in Running Count

How do we convert the formula in cell D2 to an array formula so that it can expand without manual interaction, I mean dragging it down.

We have used the MAP function for that.

The syntax of the MAP function is:

MAP(array1, [array2, ...], LAMBDA)

The array1 is B2:B and array2 is C2:C.

=MAP(
     B2:B,C2:C,
     LAMBDA(

We need to use the COUNTIFS formula within this lambda. We should not copy-paste as it is. What we should do is first define names representing the two arrays and replace them within the COUNTIFS.

=MAP(
     B2:B,C2:C,
     LAMBDA(first,last,

We have named B2:B with first and C2:C with last. Now, let’s update the COUNTIFS formula and paste it inside the above lambda.

=MAP(
     B2:B,C2:C,
     LAMBDA(first,last,
     ARRAYFORMULA(SUM(COUNTIFS($B$2:first&$C$2:last,{first&last,last&first})))
))

This formula fixes interchanged first and last names and returns the running count.

The above formula needs one more tweak as it may return the running count of blank cells in the first and last name columns. The IF logical test in my main formula does that.

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.