Using Offset to determine Column range in Array formula

Hi all,

I've been searching the group/forum and can't find what I'm looking for though I might be approaching this wrong.

I have an array worksheet function in the format {=SUM((Range1 = Crit 1)*(Range2 = Crit2)*(Range3 = Crit3) * (Range4 = Crit4) *RangeToSum)} Range1 to Range4 are hardcoded ranges and I originally had hard coded the RangeToSum column and it works fine. I've now had to add the flexibility to change what column RangeToSum is.

The data is in this format:
Crit1Col ... Crit2Col ... Crit3Col ... Crit4Col ... Mth1 ... Mth2 (etc to Mth12) - Where Mth1 to Mth12 is the RangeToSum columns.

What I need to do is depending on the month selected via a combobox, select the column for that Month. I've created this formula: =OFFSET(Mth1Col,0,MATCH(MonthSelected,RowOfMonths,0)-1) and subtituted it in the Array for the RangeToSum range.

Now i've read that the OFFSET formula is supposed to return a range, so by my reckoning if I substitute the RangeToSum hardcoded range with that formula it should work ... but it doesn't.

If I write the formula as =SUM(OFFSET(Mth1Col,0,MATCH(MonthSelected,RowOfMonths,0)-1) then it sums the entirity of the column for that month .... which shows it's working out the offset correctly.

When I use the Evaluate option under Formula Auditing, it puts the correct range into =SUM(CorrectRange), but when I take the =SUM() out then it comes back with the value corresponding to row that the formula has been entered (e.g. put the =SUM(OFFSET(....)) into A15 on Sheet1 it returns the value on row 15 of the column of the range on Sheet2 where the data is).

What I need to work out is how to get the OFFSET formula to return a range that will work with the original {=SUM((Range1 = Crit 1)*(Range2 = Crit2)*(Range3 = Crit3) * (Range4 = Crit4) *RangeToSum)} array formula OR can you tell me where i'm going wrong and if I need to rewrite the formula from scratch I don't mind?

I hope I've explained myself sufficiently.

Thanks in advance!

Peter M.

Try the following...

=SUMPRODUCT(--(A2:A10=Crit 1),--(B2:B10=Crit 2),--(C2:C10=Crit 3),--(D2:D10=Crit 4),INDEX(E2:P10,0,MATCH(MonthSelected,E1:P1,0)))

OR

=SUMPRODUCT((A2:A10=Crit 1)*(B2:B10=Crit 2)*(C2:C10=Crit 3)*(D2:D10=Crit 4)*(E2:P10)*(E1:P1=MonthSelected))

Hope this helps!

Originally Posted by downforce

Hi all,

I've been searching the group/forum and can't find what I'm looking for though I might be approaching this wrong.

I have an array worksheet function in the format {=SUM((Range1 = Crit 1)*(Range2 = Crit2)*(Range3 = Crit3) * (Range4 = Crit4) *RangeToSum)} Range1 to Range4 are hardcoded ranges and I originally had hard coded the RangeToSum column and it works fine. I've now had to add the flexibility to change what column RangeToSum is.

The data is in this format:
Crit1Col ... Crit2Col ... Crit3Col ... Crit4Col ... Mth1 ... Mth2 (etc to Mth12) - Where Mth1 to Mth12 is the RangeToSum columns.

What I need to do is depending on the month selected via a combobox, select the column for that Month. I've created this formula: =OFFSET(Mth1Col,0,MATCH(MonthSelected,RowOfMonths,0)-1) and subtituted it in the Array for the RangeToSum range.

Now i've read that the OFFSET formula is supposed to return a range, so by my reckoning if I substitute the RangeToSum hardcoded range with that formula it should work ... but it doesn't.

If I write the formula as =SUM(OFFSET(Mth1Col,0,MATCH(MonthSelected,RowOfMonths,0)-1) then it sums the entirity of the column for that month .... which shows it's working out the offset correctly.

When I use the Evaluate option under Formula Auditing, it puts the correct range into =SUM(CorrectRange), but when I take the =SUM() out then it comes back with the value corresponding to row that the formula has been entered (e.g. put the =SUM(OFFSET(....)) into A15 on Sheet1 it returns the value on row 15 of the column of the range on Sheet2 where the data is).

What I need to work out is how to get the OFFSET formula to return a range that will work with the original {=SUM((Range1 = Crit 1)*(Range2 = Crit2)*(Range3 = Crit3) * (Range4 = Crit4) *RangeToSum)} array formula OR can you tell me where i'm going wrong and if I need to rewrite the formula from scratch I don't mind?

I hope I've explained myself sufficiently.

Thanks in advance!

Peter M.