Changing the Sum Range

[Guest]

I have an "array formula" that uses multiple criteria to do mimic a "SUMIF"
with more than one possible criteria.

Essentially it is {SUM(IF((C3:C15<>"X")*(C3:C15<>"Y"),1,0)*(D315))}

I want to change the sum range (that is, the (D315)) dependent on some
variable. That is, sometimes I want it to use E3:E15 or K3:K15. Is there an
easy way to do this?

Thanks!

 

[Rodrigo Ferreira]

Try something like this

SUMPRODUCT( (C3:C15<>"X")* (C3:C15<>"Y"), OFFSET(C3;;D1;13))

where 13 is the number of cells you want to sum

where D1 is the number of the column (after C) you want to sum.
eg.:
if D1 = 1, will sum D315
if D1 = 8, will sum K3:K15


If you want everything in variables, try this:

SUMPRODUCT( (OFFSET(C3;;C1;13)<>"X")* (OFFSET(C3;;C1;13)<>"Y"),
OFFSET(C3;;D1;13))
where C1 is the number of the column (after C) you want to compare.
eg.:
if C1 = 0, will check C3:C15
if C1 = 2, will check E3:E15

 

[Bruno Campanini]

I have an "array formula" that uses multiple criteria to do mimic a "SUMIF"
with more than one possible criteria.

Essentially it is {SUM(IF((C3:C15<>"X")*(C3:C15<>"Y"),1,0)*(D315))}

I want to change the sum range (that is, the (D315)) dependent on some
variable. That is, sometimes I want it to use E3:E15 or K3:K15. Is there
an
easy way to do this?

{=SUM((C3:C15<>"X")*(C3:C15<>"Y")*(D315))}
FormulaArray

{=SUM((C3:C15<>"X")*(C3:C15<>"Y")*INDIRECT(B3&"3:"&B3&"15"))}
FormulaArray, having in B3: D | E | K | ...

Bruno

 

[Bruno Campanini]

{=SUM((C3:C15<>"X")*(C3:C15<>"Y")*(D315))}
FormulaArray

{=SUM((C3:C15<>"X")*(C3:C15<>"Y")*INDIRECT(B3&"3:"&B3&"15"))}
FormulaArray, having in B3: D | E | K | ...

Bruno

You can avoid FormulaArray mode replacing SUM with
SUMPRODUCT.

Bruno

 

[Guest]

That works great! Muito obrigado.

Try something like this

SUMPRODUCT( (C3:C15<>"X")* (C3:C15<>"Y"), OFFSET(C3;;D1;13))

where 13 is the number of cells you want to sum

where D1 is the number of the column (after C) you want to sum.
eg.:
if D1 = 1, will sum D315
if D1 = 8, will sum K3:K15


If you want everything in variables, try this:

SUMPRODUCT( (OFFSET(C3;;C1;13)<>"X")* (OFFSET(C3;;C1;13)<>"Y"),
OFFSET(C3;;D1;13))
where C1 is the number of the column (after C) you want to compare.
eg.:
if C1 = 0, will check C3:C15
if C1 = 2, will check E3:E15

 

[Guest]

Thanks for the info! That indirect function will definitely come in handy.