Search code examples
google-sheetssumifs

How to sum a row in google sheets without taking the Nth column (N depending on the row number)?

I'm currently trying to sum some numbers in a single row, but they can't sum a number where the column number + N equals the row number.

This is my current sheet (it is not completed yet):

Current sheet

The darker column needs to sum the row to its right but ignoring the values where row not equals column + 1.

The best I could come up with was the following: =SUMIF(C2:AA2, NE(ROW(), COLUMN()+1)), for row 2 (the other rows have a regular sum). But I've noticed that ROW() and COLUMN() only apply to the current cell (in this case B2, thus the resulting 0). So how do I ignore the values from a range of cells where NE(ROW(), COLUMN()+1) for each of them?

For the cell B2 the result should be 3.15, in this case, ignoring the cell C2 because ROW() == COLUMN()+1.

Solution

  • Either in a different sheet or insert an extra Column to the right of AA and in Row2 there, copied down to suit:

    =sum(B2:AA2)-offset(B2,,row()-2)

    The row number is driving the column offset for the cell to "unsum".