SUMIF Formula Help

[coughling]

I am currently using excel to track my store inventory.

I enter the current inventory count on one sheet and then break the inventory up on seperate sheets for each vendor I shop at, so I have a single source to review for purchases I need to make.

The problem I have encountered is that each time I do inventory I add 2 extra columns to show the new inventory and what I have used. I do not currently track purchases on the spreadsheet.

The original formula is: =SUMIF('Inventory Sheet'!B2:B152, A3,'Inventory Sheet'!F2:F152)
Every time I copy it and move it over 2 columns it changes the B2:B152 over to colums.

Here is the original formula used: =SUMIF('Inventory Sheet'!B2:B152, A3,'Inventory Sheet'!F2:F152)
Here is an example of it copied over: =SUMIF('Inventory Sheet'!D2:D152, C3,'Inventory Sheet'!H2:H152)
Here is what I need it to be coppied over: =SUMIF('Inventory Sheet'!B2:B153, A3,'Inventory Sheet'!H2:H152)

It gets worse when I try and copy the formula down the column.
Here is the formula as I copy it down. The reference changes.
=SUMIF('Inventory Sheet'!B3:B154, A4,'Inventory Sheet'!H3:H153)
=SUMIF('Inventory Sheet'!B4:B155, A5,'Inventory Sheet'!H4:H154)
=SUMIF('Inventory Sheet'!B5:B156, A6,'Inventory Sheet'!H5:H155)

The basic formula is sumif(range, criteria,[sum_range]


Is there a way to keep the range and [sum_range] the same and only change the criteria?

I have 7 seperate sheets for the different vendors I use.

Thanks.

[63falcondude]

Hello and welcome to the forum.

Include absolute reference symbols ($) before the reference that you want to keep static.

For example:

=A1 will change both columns and rows when copied across or down
=$A1 will change rows when copied down rows
=A$1 will change columns when copied across columns
=$A$1 will not change either columns or rows when copied across or down