MrExcel Publishing
Your One Stop for Excel Tips & Solutions

A twist to my earlier array problem


Posted by Russell Hauf on November 20, 2001 11:36 AM

Referring to the formula:

=SUMPRODUCT((Sheet1!A1:A10=A1)*(Sheet1!B1:B10=B1)*(Sheet1!C1:C10=C1)*(Sheet1!D1:D10))

that was given as an answer to my earlier post (link below),
could I change this formula to dynamically select a column other than D? So suppose I want to use cell G1 on Sheet2 to hold the column number of the number I want to return.

So the part that needs to change is:

(Sheet1!D1:D10)

I know that I could use the ADDRESS function along with FIND, LEFT, and COLUMN to change the D, like LEFT(ADDRESS(1,COLUMN($G$1),2),FIND("$",C50)-1) --

and then use INDIRECT to change function where the D1:D10 is, but is there a better way?

Thanks again,

Russell


Posted by Aladin Akyurek on November 20, 2001 11:53 AM

Russell --

You can name the ranges of interest in Sheet1:

Sheet1!A1:A10 --> aRange (or better: a meaningful name)
Sheet1!B1:B10 --> bRange
....
Sheet1!G1:G10 --> gRange

In G1 enter: gRange (or any other name of the named range) and use instead:

=SUMPRODUCT((aRange=A1)*(bRange=B1)*(cRange=C1),(INDIRECT(G1)))

assuming that gRange is a range that must be summed.

Aladin


Posted by Russell Hauf on November 20, 2001 12:09 PM

That's a good idea, but the number of rows will change in this sheet from month to month. I guess I could name the ranges dynamically from VBA each month (I have other code that will run anyway).
Thanks again for your help, you are truly an Array Formula Master!

-rh --


Posted by Juan Pablo on November 20, 2001 12:15 PM

But, without VBA you can also make them Dynamic using OFFSET, something like:

=OFFSET(G1,0,0,COUNTA(G:G))

This will make grange, or whatever you name it, go from G1 to the last row where G has data.

Juan Pablo


Posted by Russell Hauf on November 20, 2001 12:25 PM

Great idea, thank you. But, without VBA you can also make them Dynamic using OFFSET, something like: =OFFSET(G1,0,0,COUNTA(G:G)) This will make grange, or whatever you name it, go from G1 to the last row where G has data. Juan Pablo : That's a good idea, but the number of rows will change in this sheet from month to month. I guess I could name the ranges dynamically from VBA each month (I have other code that will run anyway).


Posted by Aladin Akyurek on November 20, 2001 12:28 PM

Named dynamic ranges...

=OFFSET(G1,0,0,COUNTA(G:G)) This will make grange, or whatever you name it, go from G1 to the last row where G has data.

I aggree with Juan. However, you have multiple ranges to consider, so you need to reduce to minimize using volatile functions like COUNTA.

Activate Insert|Name|Define.
Enter MaxRecs as name in the Names in the Workbook.
Enter in the Refers To box:

=MATCH(9.99999999999999E+307,Sheet1!$D:$D)

I've chosen a column of numeric type intentionally.

Now activate A1 in Sheet1.
Activate Insert|Name|Define.
Enter aRange (or a better meaningful name) in the Names in the Workbook box.
Enter in the Refers To box:

=OFFSET(Sheet1!$A$1,0,0,MaxRecs,1)

Repeat this proc for the rest of ranges of interest.

Now you can use aRange everywhere in your workbook.

Aladin

========= : That's a good idea, but the number of rows will change in this sheet from month to month. I guess I could name the ranges dynamically from VBA each month (I have other code that will run anyway).


Posted by Russell Hauf on November 20, 2001 12:57 PM

Re: Named dynamic ranges...


Posted by Russell Hauf on November 20, 2001 1:01 PM

Re: Named dynamic ranges...(sorry, last post empty)

(what a rookie I am)

Another good idea, Aladin - however, I can't seem to get the dynamic ranges to work in my SUMPRODUCT formula. I can get them to work when I explicitly define the ranges, though.

Ex:

My range is named rng4, and I have rng4 in cell G1. When I try the sumproduct formula:

=SUMPRODUCT((aRange=A1)*(bRange=B1)*(cRange=C1),(INDIRECT(G1))), I get #REF!.

I think I'll just define the names in VBA...


Posted by Aladin Akyurek on November 20, 2001 3:04 PM

Re: Named dynamic ranges...

Mea culpa. That boils down to a second level of direction, which will end up in #REF! indeed. I think I'll just define the names in VBA...

How about using a condition, e.g.,

=SUMPRODUCT((aRange=A1)*(bRange=B1)*(cRange=C1),(IF(G1="Rng4",Rng4,Rng5)))

Would this be sufficient?

Otherwise, you could try your original idea using indirect and address.

Aladin

Posted by Russell Hauf on November 20, 2001 3:11 PM

Re: Named dynamic ranges...

Thank you. I think I'll just set the ranges at run-time each month. It should take all of about 3 seconds. I appreciate your time.

Russell