Using dynamic range names in SUMPRODUCT.

Philem

Active Member
Joined
Jan 2, 2003
Messages
282
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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Aladin Akyurek said:
Care to post the SumProduct formula that you use?
As requested:
Code:
=SUMPRODUCT((Y16:Y16<>"")*(Q16:Q16))
is what is being used now, and works fine. The desired formula is
Code:
=SUMPRODUCT((DynRange1<>"")*(DynRange2))
for an example.

Many thanks.

P
 
Upvote 0
Philem said:
Aladin Akyurek said:
Care to post the SumProduct formula that you use?
As requested:
Code:
=SUMPRODUCT((Y16:Y16<>"")*(Q16:Q16))
is what is being used now, and works fine. The desired formula is
Code:
=SUMPRODUCT((DynRange1<>"")*(DynRange2))
for an example.

Many thanks.

P

DynRange1 and DynRange2 must be of equal size. Are they?
 
Upvote 0
Hi P,

Are you checking that the dynamic ranges have the same dimensions?

From Help:
The array arguments must have the same dimensions. If they do not, SUMPRODUCT returns the #VALUE! error value.
 
Upvote 0
Aladin and Richie,

Yes, the ranges are declared EXACTLY the same, and have the same number of non null cells. I knew to check that, at least. :oops:

Perhaps it's not to be. Ah well.

Thanks!

P
 
Upvote 0
Philem said:
Aladin and Richie,

Yes, the ranges are declared EXACTLY the same, and have the same number of non null cells. I knew to check that, at least. :oops:

Perhaps it's not to be. Ah well.

Thanks!

P

Don't worry. SumProduct as any other function works with dynamic ranges.

Does DynRange2 house any text value or a formula returned ""?
 
Upvote 0
<SNIP>
Aladin Akyurek said:
Don't worry. SumProduct as any other function works with dynamic ranges.

Does DynRange2 house any text value or a formula returned ""?

No. DynRange2 houses dollar amounts, and DynRange 1 houses numeric. Perhaps that is the answer:
Code:
=SUMPRODUCT((DynRange1>0)*(DynRange2))
instead of testing for <>""?

P
 
Upvote 0
Philem said:
<SNIP>
Aladin Akyurek said:
Don't worry. SumProduct as any other function works with dynamic ranges.

Does DynRange2 house any text value or a formula returned ""?

No. DynRange2 houses dollar amounts, and DynRange 1 houses numeric. Perhaps that is the answer:
Code:
=SUMPRODUCT((DynRange1<0)*(DynRange2))
instead of testing for <>""?

P

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?
 
Upvote 0
SOLVED!

Aladin Akyurek said:
<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
 
Upvote 0

Forum statistics

Threads
1,213,537
Messages
6,114,216
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top