Dynamic ranges in conjunction with array formulas?

[Dan The Man]

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

 

[Oaktree]

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

 

[Dan The Man]

If I use the following:

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

I get:

#VALUE!

What am I doing wrong?

 

[fairwinds]

hi,

I guess you have a header screwing it up.

Try:

=SUMIF(RefRange,"1",DataRange)

 

[Dan The Man]

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?

 

[fairwinds]

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.

 

[just_jon]

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)

 

[Dan The Man]

And now it all makes sense.

Apoglies, I now understand the previous posts.

 

[Dan The Man]

or rather apologies.