Search code examples
excelrowformulasumifs

update formula row and column while dragging

I have listed out data row-wise and I wish to consolidate the data column-wise as shown.

image


In cell G4, I used the formula SUMIFS($C$4:$C$13,$A$4:$A$13,F3,$B$4:$B$13,E4)

I have to update the formula manually (row and column index lookup) each time when I drag it row wise and column wise. How is it possible to make the formula automatically adapt to the header and row when I drag diagonally to get the sum of all items as shown in output format?

Solution

  • Without knowing a little more about what you're trying to do, I can't tell you exactly which parts of your formula need to change, however:

    The parts of the cell references that have $ "dollar signs" before them will not change when you "drag" the cell.

    As an example:

    $F$3 will always refer to Cell F3, no matter where you copy or drag it.

    $F3 will always refer to Column F, no matter where you copy or drag it (but the Row [3] will change depending on where you copy or drag it.)

    F$3 will always refer to Row 3, no matter where you copy or drag it (but the Column [F] will change depending on where you copy or drag it.)

    ☆ See the "Crash Course" (bottom part) of my answer to this question.

    More reading: