• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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
 

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.
 
Top