How to sum the most recent 3 matching entries across multiple columns in Google Sheets

[Laura219]

I’m working with a data table that spans from column A to BL and keeps getting new rows added over time. I need help creating a formula that can look at column D and find the last three instances where it says “English”.

What I want to do is:

• Find those 3 most recent “English” entries in column D
• Get the corresponding data from columns E through BL for those rows
• Add up all the values so I get one combined row instead of three separate ones.

For example, if the last 3 English rows have values like 2, 2, 2 in each column, I want my result to show 6 in each column instead of getting three different rows.

I tried using this formula but it’s not working: =XLOOKUP(SEQUENCE(3), ROW(D2:D), INDEX(FILTER(E2:BL, D2:D="English"), ), , -1)

I’m looking for a formula solution rather than using Apps Script. I’m open to using QUERY, SUM, INDEX or other functions if they work better for this problem.

[SoaringEagle]

have u tried using =SUM(FILTER(E2:BL, D2:D="English"))? it should add them up directly, just make sure to adjust the range if needed. good luck!

[benmoore]

Your current approach won’t work because you’re mixing functions that don’t play nice together for this task. You need MMULT with array formulas to sum the last 3 matching rows properly. Try this: =MMULT((SEQUENCE(ROWS(D2:D),1,ROWS(D2:D),-1)<=3)*(D2:D="English"),E2:BL). Here’s what it does: creates a reverse sequence to grab the most recent entries, filters for “English” matches, then multiplies through the values in columns E to BL. MMULT handles summing across multiple columns at once. Just make sure your data range covers all your rows and adjust the D2:D and E2:BL references as your dataset grows. This’ll get you exactly what you want without a bunch of helper columns.

[scarlettturner]

The Problem:

You’re trying to sum the values in columns E through BL for the three most recent rows where column D contains “English”. Your current formula using XLOOKUP and SEQUENCE isn’t working because it doesn’t correctly handle the dynamic filtering and ordering required to identify the three most recent occurrences of “English” in column D. You need a solution that efficiently identifies these rows, extracts the corresponding data from columns E through BL, and then sums those values for each column.

Understanding the “Why” (The Root Cause):

The challenge lies in combining several operations: filtering for “English” in column D, identifying the three most recent matches, extracting data from other columns for those rows, and finally summing the extracted data for each column. Functions like XLOOKUP and SEQUENCE are not designed to handle this combination of dynamic filtering and array operations efficiently. A more suitable approach involves leveraging functions that can work directly with arrays and handle conditional logic effectively.

Step-by-Step Guide:

Step 1: Use SUMPRODUCT and LARGE to Sum the Last Three “English” Rows

This approach uses SUMPRODUCT to efficiently sum the values based on conditions and LARGE to identify the row numbers of the three most recent “English” entries. The solution will need to be replicated for each column from E to BL.

For column E, use the following formula:

=SUMPRODUCT((LARGE((D2:D="English")*ROW(D2:D),{1;2;3})=ROW(D2:D))*(E2:E))

This formula works as follows:

1. (D2:D="English")*ROW(D2:D): This part creates an array where each element is the row number if the corresponding cell in column D is “English”, and 0 otherwise.
2. LARGE((D2:D="English")*ROW(D2:D),{1;2;3}): This extracts the three largest row numbers from the array created in step 1. These are the row numbers of the three most recent “English” entries.
3. LARGE(...)=ROW(D2:D): This part creates an array that evaluates to TRUE if the row number is one of the three most recent “English” rows, and FALSE otherwise.
4. *(E2:E): This multiplies the array from step 3 with the values in column E. This ensures that only the values from the three most recent “English” rows are considered.
5. SUMPRODUCT(...): This finally sums the resulting array, giving you the total for column E.

Step 2: Replicate for Columns F to BL

Copy the formula from Step 1 across to columns F through BL. Make sure to adjust the (E2:E) part of the formula to (F2:F), (G2:G), and so on, for each respective column.

Step 3: Adjust Ranges as Needed

As your data grows, you will likely need to adjust the ranges (e.g., D2:D, E2:E) in the formula to encompass all your data.

Common Pitfalls & What to Check Next:

• Range Sizes: Ensure that the ranges in your SUMPRODUCT formulas (e.g., D2:D, E2:E) are large enough to include all your data, including future rows. If the ranges are too small, the formula will not correctly calculate the sum.
• Data Consistency: Make sure that the “English” entries in column D are consistently formatted (e.g., no extra spaces). Inconsistent formatting can prevent the formula from correctly identifying the matches.
• Case Sensitivity: The formula is case-sensitive. If “English” could appear as “english” in your data, use LOWER(D2:D)="english" instead of D2:D="English" to make it case-insensitive.

Still running into issues? Share your (sanitized) config files, the exact command you ran, and any other relevant details. The community is here to help!