google-sheetsfilterlambdasumgoogle-query-language

Return SUM of all cells that fit TWO CRITERIA related by POSITION

I'm able to use SUMIF to return sum of all cells that fit one criteria:

=SUMIF(DATA!$U$3:$3, "Previous Age",DATA!$U9:9)

However, I'd like to add another criteria: to match $F$2 with 1st criteria's OFFSET (,,-3).

=SUMIF(DATA!$U$4:$4, DATA!$U$3:$3&OFFSET(DATA!$U$3:$3,,-3),"Previous Age"&$F$2)

=SUMIF(DATA!$U$4:$4, (DATA!$U$3:$3="Previous Age")*(OFFSET(DATA!$U$3:$3,,-3)=$F$2))

The above two formulas do not work, but hoping it still conveys what I'm trying to do.

the cells in question are highlighted in teal.

attached is the file

Solution

  • In this case SUMIF can't be used as its 2nd parameter must be a valid range otherwise you will get:

    ERROR Argument must be a range.

    Try:

    =SUM(LET(x, WRAPROWS(FILTER(DATA!U4:4, 
 REGEXMATCH(DATA!U3:3, "Previous Age|Rope Type")), 2, ), 
 IFNA(FILTER(INDEX(x,,1), INDEX(x,,2)=F$2))))

    enter image description here

    Basically it's extraction of columns wrapped into table and filtered down before summing it:

    enter image description here

    Update

    =SUM(LET(x, WRAPROWS(FILTER(DATA!AA4:4, 
 REGEXMATCH(DATA!AA3:3, "Previous Age|Rope Type")), 2, ), 
 IFNA(FILTER(INDEX(x,,2), INDEX(x,,1)=F$2))))

    enter image description here