Search code examples
matrixgoogle-sheetssumifs

Sumifs in a matrix with one line-condition and two column-conditions


These are the pictures I'm working with, the formula I tried to use, and the error pop up

I'm trying to collect information from a table (in blue, B2:J9) to distribute it in a line (in red, B15:AB15) according to three conditions (box in green, B11:AB13).

The difficulty, in this case, is that some conditions have to do with columns and others with rows in the matrix, so it is not possible to convert the matrix into a string to do the sum.

The formula I tried is

=sumifs(D3:J9,D2:J2,B11,C3:C9,B12,B3:B9,B13)

And the error was "Array arguments to SUMIFS are of different size."

The following is the picture with two expected values, the first one (in yellow) and the last one (in pink)

enter image description here


Solution

  • paste in B15 and drag to the right:

    =IFERROR(SUMPRODUCT(QUERY(TRANSPOSE(QUERY(TRANSPOSE($B$2:$J$9), 
     "where Col1='"&B11&"'", 2)), 
     "where Col1='"&B13&" "&B12&"'", 0)), )
    

    0