This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Dear Members,
when I use sumif function it work accurate in first cell when i copy and paste to other cell it changes the value, as its very difficult to copy and paste and then alter the formula accordingly.
| e.g : when i copy and paste sumif in three cells so 1st cell calculate right but in second cell it goes to next in sum range <br><br> <br><br> <br><br>Wrong: <br><br>=SUMIF(Old!C2:C7815,Sheet6!C2,Old!I2:I7815) |
|---|
| =SUMIF(Old!C3:C7816,Sheet6!C3,Old!I3:I7816) |
| =SUMIF(Old!C4:C7817,Sheet6!C4,Old!I4:I7817) <br><br> <br><br>when i right by self then it work right <br><br>Right : |
| =SUMIF(Old!C2:C7815,Sheet6!C2,Old!I2:I7815) |
|---|
| =SUMIF(Old!C3:C7816,Sheet6!C3,Old!I3:l7815) |
| =SUMIF(Old!C4:C7817,Sheet6!C4,Old!I4:l7815) |
when I use sumif function it work accurate in first cell when i copy and paste to other cell it changes the value, as its very difficult to copy and paste and then alter the formula accordingly, any body may help ??
| e.g : when i copy and paste sumif in three cells so 1st cell calculate right but in second cell it goes to next in sum range <br><br> <br><br> <br><br>Wrong: <br><br>=SUMIF(Old!C2:C7815,Sheet6!C2,Old!I2:I7815) |
|---|
| =SUMIF(Old!C3:C7816,Sheet6!C3,Old!I3:I7816) |
| =SUMIF(Old!C4:C7817,Sheet6!C4,Old!I4:I7817) <br><br> <br><br>when i right by self then it work right <br><br>Right : |
| =SUMIF(Old!C2:C7815,Sheet6!C2,Old!I2:I7815) |
|---|
| =SUMIF(Old!C3:C7816,Sheet6!C3,Old!I3:l7815) |
| =SUMIF(Old!C4:C7817,Sheet6!C4,Old!I4:l7815) |
Any body please help me in this matter.
Best Regards,
Hasan
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Answer accepted by question author
You need to use absolute addressing to keep the row numbers from changing.
=SUMIF(Old!C**$2:C$7815,Sheet6!C2,Old!I$2:I$**7815)
If you also need to copy/fill the formula to the right, then also make the columns absolute:
=SUMIF(Old!$C$2:$C$7815,Sheet6!$C2,Old!$I$2:$I$7815)
Hi,
I am still confuse SUMIF formula copy paste...
counting
=SUMIF(STK!A2:B3563,A2,STK!B2:B3563)
copy paste next line
=SUMIF(STK!A3:B3564,A3,STK!B3:B3564)
=SUMIF(STK!A4:B3565,A4,STK!B4:B3565)
I want to paste same formula keep row different...
=SUMIF(STK!A2:B3563,A2,STK!B2:B3563)
=SUMIF(STK!A2:B3564,A3,STK!B2:B3564)
=SUMIF(STK!A2:B3565,A4,STK!B2:B3565)
The above formula I have to do manual (bold) for counting...
Master sheet, Formula at Stock cell
| Item No | Item Name | OEM# | Stock |
|---|---|---|---|
| 0700-00-002 | N. B. Terminal | 1901 315 015 | 23 |
| 0700-00-003 | P. B. Terminal | 1901 315 014 | 16 |
| 0700-03-025 | NP Lamp | 26510 | 52 |
STK
| A-Item No | B-QTY | Item Name | OEM No |
|---|---|---|---|
| 0700-00-002 | 12 | N. B. Terminal | 1901 315 015 |
| 0700-00-002 | 5 | N. B. Terminal | 1901 315 015 |
| 0700-00-002 | 6 | N. B. Terminal | 1901 315 015 |
| 0700-00-003 | 10 | P. B. Terminal | 1901 315 014 |
| 0700-00-003 | 5 | P. B. Terminal | 1901 315 014 |
| 0700-00-003 | 1 | P. B. Terminal | 1901 315 014 |
| 0700-03-025 | 47 | NP Lamp | 26510 (215.2103 |
| 0700-03-025 | 1 | NP Lamp | 26510 (215.2103 |
| 0700-03-025 | 4 | NP Lamp | 26510 (215.2103 |