SUMPRODUCT of last nth values with criteria-CodePudding

I have two columns (see screenshot)

How can i create a formula that sum the second LATEST column values with a criteria from column A?

For example i need the sum of the last 6 values (from column B) of the cells (in column A) that start with HH, so values starting from the bottom. I know how to make a sum of all values (from column B) containing HH (from column A)

=SUMIF(A1:A;"HH"&"*";B1:B)

P.S. HH and * are separate because i'll substitute the HH with a cell

but now i need to delimit this to the last N values (let say last 3 values)

P.P.S.

=SUMPRODUCT((COUNTIFS(A1:A;"exact text";ROW(A1:A)*{1;1};">="&ROW(A1:A)*{1;1})<=3)*(A1:A="exact text");B1:B)

This works so far ONLY if i write the exact text, not with values like HH*

CodePudding user response：

Maybe try

=sum(index(query({row(A1:A), A1:B}, "Select Col3 where Col2 contains 'HH' order by Col1 desc limit 6")))

and see if that works?

Note:

*the string HH can be also be in a cell (ex. D1)

=sum(index(query({row(A1:A), A1:B}, "Select Col3 where Col2 contains '"&D1&"' order by Col1 desc limit 6")))

*6 indicates the number of values you want to sum

EDIT: For your locale you'll need to use in G1

=sum(index(query({row($B$1:$B) \ $B$1:$C}; "Select Col3 where Col2 contains '"&E2&"' order by Col1 desc limit 3")))

and fill down. See if that works?

CodePudding user response：

This should also work, not sure if it's any easier to understand/ less complicated than any other approach:

=SUM(SORTN(REGEXMATCH(B:B;E2)*C:C;3;0;ROW(B:B)*REGEXMATCH(B:B;E2);0))

Note the number 3 for the number of values you want from the bottom. and the reference to E2, which is "HH" as on your sample sheet.

CodePudding user response：

use:

=QUERY(FILTER({IFNA(REGEXEXTRACT(SORT(B2:B; ROW(B2:B); 0); 
 "^([A-Za-z]{1,3})\d"))\SORT(C2:C; ROW(B2:B); 0)}; COUNTIFS(
 REGEXEXTRACT(SORT(B2:B; ROW(B2:B); 0); "^([A-Za-z]{1,3})\d"); 
 REGEXEXTRACT(SORT(B2:B; ROW(B2:B); 0); "^([A-Za-z]{1,3})\d"); 
 ROW(H2:H43); "<="&ROW(H2:H43))<=3); 
 "select Col1,sum(Col2) group by Col1 label sum(Col2)''")

full explanation