SUMIFS using Indirect

[cmb80]

Hi, I have a formula that works - but when I drag it to the cells to the right the "SUMIFS" column remains fixed? I want this to change by one as it would if not using Indirect function.

For instance, the first part of my code in cell I3 is

Please Login or Register  to view this content.

now if I drag it to J3 it stays exactly the same, I want it to change to

Please Login or Register  to view this content.

Can anyone assist in what needs to change to allow it to do that? There are no $ in the formula which normally lock the column

[AliGW]

Try this:

=SUMIFS(INDIRECT($C$2&"!"&AR:AR)

[cmb80]

Hi, thanks for the reply - that gives me a #REF error

[cmb80]

Just to clarify, cell C2 has the sheet name to get the sum from

[Glenn Kennedy]

Post a sample sheet, please.

[cmb80]

Example file attached. Highlighted in yellow is the code I have altered manually and looking to be able to drag along

[Glenn Kennedy]

Never mind. This is what you need:

=SUMIFS(INDIRECT("'"&$C$2&"'!"&ADDRESS(1,COLUMNS($A:AR),4)&":"&ADDRESS(10,COLUMNS($A:AR),4)),$D$1:$D$10,$F$1)

Choose the rows sensibly, by changing the bits in red, unless you DO have 1 million rows, don't use whole column references.

[cmb80]

Now attached, previous was too big

[jason.b75]

if I drag it to J3 it stays exactly the same There are no $ in the formula which normally lock the column

That is because "AR:AR" is a text string, not a cell reference. To make it relative, you either need to mix and match functions as Glenn has done, or use R1C1 notation (which some people find confusing).

Please Login or Register  to view this content.

C[35] is R1C1 notation for an offset of 35 columns to the right (AR is 35 columns from I3, AS is 35 columns from J3, etc).
The FALSE arguement tells INDIRECT that the range used is R1C1 instead of a standard A1 reference.

edit:- in normal use, it is not unreasonable to use full columns with SUMIFS, however, looking at the size of your sample file alon with the fact that you had to shrink it to atach it, I would suggest that you heed Glenn's warning to use a realistic number of rows.

Please Login or Register  to view this content.

This covers rows 2 to 2000, note that the row numbers are not enclosed in square brackets. Omitting the square brackets in R1C1 is the same as adding a $ to an normal reference.

[cmb80]

Thanks Jason, it works using your first formula but not your second so will go with that

[Glenn Kennedy]

I guess I became invisible here!!