3

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?

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
Melko
  • 41
  • 7
  • The cell references that have `$` Dollar Signs will not change when you "drag" the cell. – ashleedawg Feb 10 '18 at 06:31
  • If I use the $ sign for F3, when I drag it row-wise it would not refer to the first row of the output If I use $ sign for E4, when I drag column-wise it would not refer to the first column of the output. Each time, when I jump to next column I have to change the reference and then drag down. I wish to avoid the exercise of correcting the formula each time before dragging down. – Melko Feb 10 '18 at 06:42
  • 1
    `$F$3` will stay `F3` no matter where you copy or drag it. `$F3` will stay 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 stay row `3` no matter where you copy or drag it, but the column (`F`) will change depending on where you copy or drag it... – ashleedawg Feb 10 '18 at 06:48
  • after entering $ and copying the formula to G4 it is as follows =SUMIFS($C$4:$C$14,$A$4:$A$14,$F$3,$B$4:$B$14,F4) Whereas I intend it to be =SUMIFS($C$4:$C$14,$A$4:$A$14,G3,$B$4:$B$14,E4) – Melko Feb 10 '18 at 06:52
  • *More information needed please!* There are 3 different possibilities of where the dollar sign can be . When you say `with $ to G4` do you mean `$G$4` or `$G4` or `G$4` ? ...and copy *from* where and *to* where. Please read my [answer](https://stackoverflow.com/a/48717996/8112776) (below). – ashleedawg Feb 10 '18 at 06:55
  • the link to your image does not work. please post a working link. – teylyn Feb 10 '18 at 07:06
  • Please see this explanation https://imgur.com/a/ixMnq – Melko Feb 10 '18 at 07:15
  • Original image in the post can be found here https://imgur.com/okkEa9h – Melko Feb 10 '18 at 07:24
  • 1
    I moved the image to the Question from the 3rd party site. – ashleedawg Feb 10 '18 at 08:01
  • @MelvinKoshy Did you get this figured out with my explanation? – ashleedawg Feb 10 '18 at 08:01
  • 1
    I used this SUMIFS($C$4:$C$14,$A$4:$A$14,F$3,$B$4:$B$14,$E4) and it works perfectly as desired. – Melko Feb 10 '18 at 08:19
  • 2
    @MelvinKoshy If the answer was satisfactory you should accept it (✔ next to answer). – Miguel_Ryu Feb 10 '18 at 09:22

2 Answers2

4

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:

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
0

I'd suggest setting up the input information as an Excel Table.

Output then can be set as Pivot Table.

Anytime if you make changes to the source information all you need to do is refresh Pivot Table making it easier to maintain and less formula intensive. If you search up internet for Pivot Table several links will come up.

shrivallabha.redij
  • 5,832
  • 1
  • 12
  • 27