Search code examples
excelrowmultiple-columnscellssumifs

Sumif nonadjacent cells in a row

I want to calculate the sum of some cells from a row based on the value of the cell that is in front of it. See the image bellow for more details:

Sumif nonadjacent cells in a row:

enter image description here

Note that this is a simplistic version of my actual case. In my case I have hundreds of rows and tens of columns. It wouldn't be feasible to transpose the row into 2 columns, and I couldn't use nested ifs since you can use only 7. Is there any solution to this case? Any other recommendation would be most appreciated.

Thank you

Solution

  • You can use SUMPRODUCT in this method:

    =SUMPRODUCT(--(H5:V15="A"),(I5:W15))

    Notice that the two ranges are offset by one column.

    enter image description here

    Or you can also use SUMIF with offset columns:

    =SUMIF(H5:V15,"A",I5:W15)

    The only rule between the two formulas is that the ranges be the same size.