Reverse Running Count Simplified in Google Sheets

GiGiKing·March 20, 2024·0 comments

This tutorial will teach you a simplified version of the array formula to get the reverse count of occurrences of items in a list in Google Sheets. Forget about complicated formulas, because with this simplified approach, you’ll be able to achieve the same results effortlessly.

Example

Example to Reverse Running Count in Google Sheets

Imagine you have a list of names in column A, and you want to count how many times each name appears in reverse order (from the bottom of the list up). In column F, you will find the reverse count of occurrences for each name.

Practical Applications

The reverse running count formula is extremely useful in filtering and analyzing lists. Let’s take a look at two scenarios:

  1. If the list is arranged from oldest to newest, you can use the reverse running count column as a filter criterion. This will allow you to filter the list for specific conditions based on the reverse count of occurrences.

Example formula:

=filter(A2:C9,F2:F9=1)
  1. On the other hand, if the list is arranged from newest to oldest, you can use the running count column as a filter criterion.

Example formula:

=filter(A2:C9,E2:E9=1)

These formulas can help you retrieve specific data from your list based on the reverse running count.

Array Formula for Reverse Running Count in Google Sheets

The simplified way to calculate the reverse running count in Google Sheets is by using the COUNTIFS function in an array formula. Here’s how:

Formula:

=ArrayFormula(
  if( B2:B="",
    ,
    COUNTIFS(
      sort(row(B2:B),1,0),
      "<="&sort(row(B2:B),1,0),
      A2:A&"-"&B2:B,
      "="&A2:A&"-"&B2:B
    )
  )
)

In this formula, we’re primarily focusing on the COUNTIFS part to achieve the reverse running count.

Let’s break it down:

  • sort(row(B2:B),1,0): This sorts the row numbers in reverse order.
  • "<="&sort(row(B2:B),1,0): We compare the reverse sorted row numbers to the reverse sorted row numbers themselves. This helps us count the occurrences in reverse order.
  • A2:A&"-"&B2:B: This combines the names in column A and B to create a unique identifier for each occurrence.
  • "="&A2:A&"-"&B2:B: We compare the combined names to find occurrences of the same name in each row.

You may notice that the formula also contains additional parts that help with restricting the formula fill. These parts can be ignored for now.

Alternate Formula

Here’s an alternate formula that achieves the same result:

=ArrayFormula(
  if(
    B2:B="",
    ,
    COUNTIFS(
      row(B2:B),
      ">="&row(B2:B),
      A2:A&"-"&B2:B,
      "="&A2:A&"-"&B2:B
    )
  )
)

Conclusion

Now you know how to calculate the reverse running count of occurrences in Google Sheets using a simplified formula. This technique can help you analyze and filter data in a wide range of scenarios.

For more helpful tutorials and tips on Google Sheets, visit Crawlan.com. Discover new ways to level up your spreadsheet skills and make the most out of Google Sheets!

GiGiKing

Related posts