# How to lock worksheet name in a SUMIFS formula

#### blackcat

##### New Member
Hi everyone,

I'm struggling to find a way to lock some parts in my SUMIFs formula while still keep one column moving alphabetically when I drag the formula to other cells for auto calculation.

Here is the example:
=SUMIFS('4Active'!B:B,'4Active'!J:J,A1)

4Active is my worksheet name. I would like to fixate the parts of '4Active'!, '4Active'!J:J and A1, so when I drag the cell the formula will only change B:B to C:C, D:D, E:E and so on.

I know there should be a solution for that but just couldn't quite Google the right answer to my question. Would hugely appreciate if anyone can help out here. Many thanks in advance. Kay

#### AliGW

##### Active Member
Try this:

=SUMIFS('4Active'!B:B,'4Active'!\$J:\$J,\$A\$1)

#### blackcat

##### New Member
Thanks Ali unfortunately your formula didn't work. When I dragged this formula to the cells below it didn't change at all. I think what you did here is to lock the entire formula.

What I would like is to find a tweaked formula which allows the B:B part to move dynamically but lock the rest part in the formula. So for example, if I drag the formula to cells below it, the data result should appear the same as if from:
=SUMIFS('4Active'!C:C,'4Active'!J:J,A1)
=SUMIFS('4Active'!D:D,'4Active'!J:J,A1)
=SUMIFS('4Active'!E:E,'4Active'!J:J,A1)
and so on

Right now I have to manually input formula in each cell, which is clearly not the fastest way to do it...

#### AliGW

##### Active Member
No, I didn't. Look carefully:

=SUMIFS('4Active'!B:B,'4Active'!\$J:\$J,\$A\$1)

The B range does NOT have the dollar signs, so is not locked. It will change as you drag across.

#### GraH - Guido

##### Well-Known Member
Hi blackcat,

Firstly: welcome to the forum.
Secondly: please read the forum rules and understand why sample data makes live so much easier for all.

Thirdly: As I understand the issue, you are dragging down the formula. That way will never change the column references. Dragging to the side will and AliGW's formula will then work. FYI: a worksheet reference is always "Locked", so the thread title is a bit misleading.

#### bosco_yip

##### Excel Ninja
….What I would like is to find a tweaked formula which allows the B:B part to move dynamically but lock the rest part in the formula. So for example, if I drag the formula to cells below it, the data result should appear the same as if from:
=SUMIFS('4Active'!C:C,'4Active'!J:J,A1)
=SUMIFS('4Active'!D:D,'4Active'!J:J,A1)
=SUMIFS('4Active'!E:E,'4Active'!J:J,A1)
and so on
……...
Try...………….

Change your drag down formula >>

From this :

=SUMIFS('4Active'!B:B,'4Active'!J:J,A1)

Into this :

=SUMIFS(OFFSET('4Active'!A\$1,,ROW(A1),1048576),'4Active'!J:J,A\$1)

or,

You could use SUMIF instead of SUMIFS for shortened the formula length

=SUMIF('4Active'!J:J,A\$1,OFFSET('4Active'!A\$1,,ROW(A1)))

Regards
Bosco

#### blackcat

##### New Member
Try...………….

Change your drag down formula >>

From this :

=SUMIFS('4Active'!B:B,'4Active'!J:J,A1)

Into this :

=SUMIFS(OFFSET('4Active'!A\$1,,ROW(A1),1048576),'4Active'!J:J,A\$1)

or,

You could use SUMIF instead of SUMIFS for shortened the formula length

=SUMIF('4Active'!J:J,A\$1,OFFSET('4Active'!A\$1,,ROW(A1)))

Regards
Bosco
It worked! Thanks for helping out Bosco.

#### blackcat

##### New Member
Hi blackcat,

Firstly: welcome to the forum.
Secondly: please read the forum rules and understand why sample data makes live so much easier for all.

Thirdly: As I understand the issue, you are dragging down the formula. That way will never change the column references. Dragging to the side will and AliGW's formula will then work. FYI: a worksheet reference is always "Locked", so the thread title is a bit misleading.
Thanks for the tips. Well noted.