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!

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

--

Rodrigo Ferreira
Regards from Brazil


"mcleester" <(E-Mail Removed)> escreveu na mensagem
news:94B4D457-7F93-4823-B620-(E-Mail Removed)...
>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!

"mcleester" <(E-Mail Removed)> wrote in message
news:94B4D457-7F93-4823-B620-(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
news:uwZFLe$(E-Mail Removed)...

> {=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

That works great! Muito obrigado.

"Rodrigo Ferreira" wrote:

> 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
>
> --
>
> Rodrigo Ferreira
> Regards from Brazil
>
>
> "mcleester" <(E-Mail Removed)> escreveu na mensagem
> news:94B4D457-7F93-4823-B620-(E-Mail Removed)...
> >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!
>
>
>

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

"Bruno Campanini" wrote:

> "Bruno Campanini" <(E-Mail Removed)> wrote in message
> news:uwZFLe$(E-Mail Removed)...
>
> > {=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
>
>