Using dynamic range names in SUMPRODUCT.

[Philem]

Hullo. Have an issue here that I can't seem to wrap my blonde head around: In a worksheet, I'd like to set up several dynamic ranges, and use those ranges in a SUMPRODUCT formula. I know that I am setting up the dynamic ranges correctly, because I can get simpler functions (SUM, COUNTIF, and so on) to work, but when I use the SAME names in a SUMPRODUCT, I always get #VALUE.

Any suggestions appreciated. If specifics are needed, just ask!

Thanks!

P

 

[Aladin Akyurek]

Re: SOLVED!

<SNIP>

I interpret your original formula to mean:

If there is a value in DynRange1, then sum the corresponding value in DynRange2. If this is what you intend to do, a SumIf formula would suffice...

=SUMIF(DynRange1,"<>",DynRange2)

Does this give the required answer?

It does indeed. Many thanks! :wink:

P

Just to check, try also..

=SUMPRODUCT((DynRange1<>"")+0,DynRange2)

Does it work too?

 

[Philem]

Re: SOLVED!

<SNIP>
Just to check, try also..

=SUMPRODUCT((DynRange1<>"")+0,DynRange2)

Does it work too?

No, that does not work, and, would not be desired, as DynRange1 *will* have a number in it; either a zero, in which case, we ignore it, or a positive value, in which case, we use it.

Thank you for your assistance.

P

 

[Aladin Akyurek]

Re: SOLVED!

<SNIP>
Just to check, try also..

=SUMPRODUCT((DynRange1<>"")+0,DynRange2)

Does it work too?

No, that does not work, and, would not be desired, as DynRange1 *will* have a number in it; either a zero, in which case, we ignore it, or a positive value, in which case, we use it.

Thank you for your assistance.

P

Not that you should use this for a single condition summing, try...

=SUMPRODUCT((DynRange1>0)+0,DynRange2)

 

[Evangelia]

Hi both, - how do we deal with the case where the formula returns ""?