# Dynamic ranges in conjunction with array formulas?

#### Dan The Man

##### New Member
Is it possible to use dynamic ranges in conjunction with array formulas? I have been trying and I can get it to work.

What is wrong with the following simplified example (if anything at all)?

Define the ranges

'RefRange' as =OFFSET(\$A\$1,0,0,COUNTA(\$A:\$A),1) and

'DataRange' as =OFFSET(\$B\$1,0,0,COUNTA(\$A:\$A),1)

and then use array formula as follows

{=SUM((RefRange=1)*(DataRange))}

I have also used tried the following

{=SUM((OFFSET(\$A\$1,0,0,COUNTA(\$A:\$A),1)=1)*(OFFSET(\$B\$1,0,0,COUNTA(\$A:\$A),1)))}

Conclusion: Is doesn't work!

The odds are that I’m trying to do something that's impossible? The problem is I don't have a conclusive answer.

I have a massive collection of array formulas and I am trying to reduce calculation times, if only I could I get the above to work, life would be some much easier.

And before anyone tries to answer, I WILL EVENTUALY USE ALL ROWS to form the source data, hence me trying to use dynamics ranges.

Help! If Possible.

Stressed Excel User

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Conclusion: Is doesn't work!

What do you mean "I(t) doesn't work"? Are you getting a #VALUE error? You may just have labels in your first row that are throwing it off...

If I use the following:

{=SUM((RefRange=1)*(DataRange))}

I get:

#VALUE!

What am I doing wrong?

hi,

I guess you have a header screwing it up.

Try:

=SUMIF(RefRange,"1",DataRange)

I can get the sumif to work, however i have used the above only has a simple example. In practise i am having to use array formulas with several paramters. The thing is i can't get the simple example to work let alone what i'm actually trying to achieve. I must be doing something wrong.

Can anyone help?

I posted the SUMIF because it ignores text.

You would have the same effect changing:
{=SUM((RefRange=1)*(DataRange))}
into:
=SUMPRODUCT(--(RefRange=1),DataRange)

Here you could add multiple conditions.

You're getting the error because you have Text items as headers within those ranges, perhaps?

Try defining your ranges as --

=OFFSET(\$A\$1,1,0,COUNTA(\$A:\$A)-1,1)

And now it all makes sense.

Apoglies, I now understand the previous posts.

or rather apologies.

Replies
0
Views
272
Replies
1
Views
339
Replies
5
Views
411
Replies
2
Views
379
Replies
4
Views
193

1,203,137
Messages
6,053,711
Members
444,681
Latest member

### 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.

### Which adblocker are you using?

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

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