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.

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.

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.

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.