Returning the aggregate of the last 3 rows of an occurrence in google sheets

Question

I have a growing table from A:Bl. It grows by rows, not columns. I am try to write a xlookup formula that will search column D and find the last three occurrences of English

• and return those 3 rows from column E:Bl
• instead of returning the three separate rows of values. I would like one row returned that would have the the sum the 3 rows

ex. English - 5 - 5 - 5... instead of: English - 2 - 2 - 2... English - 2 - 2 - 2... English - 1 - 1 - 1...

https://docs.google.com/spreadsheets/d/1UAcQHEUvqHrIrilEgUlEQrpaOpRnbR7iOsDMIp_LRgA/edit?usp=sharing

I have tried this:=XLOOKUP(SEQUENCE(3), ROW(D2:D), INDEX(FILTER(E2:BL, D2:D="English"), ), , -1)

It doesnt work... I am looking for a formula and not app script. If Query / Sum / index Is better, those solutions would be acceptable

Answer 1

You may try this formula for one language:

=ArrayFormula(LET(lang,A2:A13,
                  toSum,B2:E13,
                  langCond,"English",
                  rowNum,ROW(lang),
                  reverseInstance,COUNTIFS(lang,lang,rowNum,">="&rowNum),
                  createArr,SEQUENCE(1,ROWS(lang),1,0),
                  total,MMULT(createArr,(lang=langCond)*(reverseInstance<=3)*toSum),
                  total))

If you need to search multiple languages, you can also try this array version:

=ArrayFormula(LET(lang,A2:A14,
                  toSum,B2:E14,
                  langCond,F5:F7,
                  rowNum,ROW(lang),
                  reverseInstance,COUNTIFS(lang,lang,rowNum,">="&rowNum),
                  total,MMULT(--(TRANSPOSE(lang)=langCond),(reverseInstance<=3)*toSum),
                  total))