Multiple sumifs with a moving target

[Mldeuser]

Hello

I am using the following sumifs statement and want to know if there is a way to account for one of the criteria changing. Currently I have to go in to each cell and update the criteria.

=SUMIFS('Accrued Expenses'!$H$11:$H$218,'Accrued Expenses'!$A$11:$A$218,"WTCEXT",'Accrued Expenses'!$D$11:$D$218,"605-100-00",'Accrued Expenses'!$C$11:$C$218,153)

If a new line is entered in the Accrued Expense spreadsheet then the numbers will need to be updated for example if a new row is inserted at row 20 then all the rows that come after the number in column C will change and the formula will need to be manually updated.

Thank you

 

[Toadstool]

Hi Mldeuser,

If you insert a row 20 the formula changes to

=SUMIFS('Accrued Expenses'!$H$11:$H$219,'Accrued Expenses'!$A$11:$A$219,"WTCEXT",'Accrued Expenses'!$D$11:$D$219,"605-100-00",'Accrued Expenses'!$C$11:$C$219,153)

so why is that a problem?

 

[Mldeuser]

Hi Mldeuser,

If you insert a row 20 the formula changes to

=SUMIFS('Accrued Expenses'!$H$11:$H$219,'Accrued Expenses'!$A$11:$A$219,"WTCEXT",'Accrued Expenses'!$D$11:$D$219,"605-100-00",'Accrued Expenses'!$C$11:$C$219,153)

so why is that a problem?

If I insert a formula in row 20 I have to add a number in C20 (in this case I enter 20 for the new row) then all the numbers below will change by one. The formula is still looking to bring back what it finds using the number 153 which now it should be looking for the row with number 154. The formula does not update when the numbers in column C change

 

[Toadstool]

Sorry but I'm still not following.
How is 153 or 154 decided?

 

[Mldeuser]

Sorry but I'm still not following.
How is 153 or 154 decided?

Lets say we have ten rows column C will have the numbers 1 through 10 in cells C1-C10. If I insert a new row at row 5, it becomes the new number five and the rows below it that were previously numbered 5-10 are now numbered 6-11. The problem I am having is the sumif formula used in another spreadsheet prior to inserting a new row recognized say cell C6 as number 6, once the row is inserted in the accrued expense tab the formula is no longer looking at the correct cell as it should be looking a the cell that has the number 7. The formula is not updating as rows are inserted and new numbers assigned or if a row is deleted and the numbers are reassigned

Hope this makes sense

The formula above is looking for the number 153 on the accrued expense tab. I a new row is inserted above the cell in column C then the number will change on the accrued expense tab, the formula will still be looking for number 153 when it should be looking for 154.

 

[Toadstool]

That is the challenge with using soft keys. Unless you change the other sheet to use a different key or make column C a hard key then I don't see how to fix it.

 

[Mldeuser]

That's what I thought, I am not able to modify the one tab. There are 8 sections on the accrued expanse tab, if i give each section its own set of character's then if something changes it will only effect one section instead of all 8.

Thank you for you help