2

I am using the following formula in an Excel sheet, that performs the SUM in a range of cells from another sheet (ITEMS CONTROL):

=SUMIF('ITEMS CONTROL'!$B$2:$B$20000,B$1,'ITEMS CONTROL'!$C$2:$C$20000)

My issue is that when inserting rows in the sheet ITEMS CONTROL, the formula changes as follows:

After inserting 18 rows:

=SUMIF('ITEMS CONTROL'!$B$20:$B$20018,B$1,'ITEMS CONTROL'!$C$20:$C$20018)

After inserting another 18 rows:

=SUMIF('ITEMS CONTROL'!$B$38:$B$20036,B$1,'ITEMS CONTROL'!$C$38:$C$20036)

What should I change to keep always the original formula?

pnuts
  • 58,317
  • 11
  • 87
  • 139
mvasco
  • 4,965
  • 7
  • 59
  • 120

2 Answers2

1

Please try: 

=SUMIF(INDIRECT("'ITEMS CONTROL'!$B$2:$B$20000"),B$1,INDIRECT("'ITEMS CONTROL'!$C$2:$C$20000"))
pnuts
  • 58,317
  • 11
  • 87
  • 139
1

Why not use whole columns like this:

=SUMIF('ITEMS CONTROL'!$B:$B$,B$1,'ITEMS CONTROL'!$C:$C)

There are no efficiency implications - SUMIF only calculates the "used range", even if you specify a bigger range

barry houdini
  • 45,615
  • 8
  • 63
  • 81