Help with Array Formula

[winnie123]

Here again,

I had a formula which looked up the qty of items a customer has ordered for
delivery within a month ie Jan, Feb etc

The formula was

=SUM((openorders!$V$2:$V$114=$C$3)*(openorders!$AD$2:$AD$114="Dec
08")*(openorders!$X$2:$X$114))

it looks at the part number on my summary sheet $C$3 against the part number
in col V in the open orders sheet. Then it looks at all the orders due out in
Dec and sums the total qty.

When the range goes larger than 114 rows, it will mean me having to
maintain all the formula's to extend the range, so I thought I could change
it to read the Last Row for each of the columns V, AD and X , so my formula
now looks like this

=SUM((openorders!$V$2&"_"&Row=$C$3)*(openorders!$AD$2&"_"&Row="Dec
08")*(openorders!$X$2&"_"&Row))

Unfortunately this gives me a #NAME error.

What have I done wrong.
Thanks
Winnie

 

[Dave Curtis]

Hi,

You can't use Row in a formula that way.
Try looking up the help for SUMPRODUCT, or try using a dynamic range.

Dave

 

[Roger Govier]

Hi Winnie

Create some Dynamic named ranges
Insert>Name>Define>
Name lr
Refers to =COUNTA($V:$V)
Name Customer
Refers to =$V$2:INDEX($V:$V,lr)
Name Month
Refers to =$AD$2:INDEX($AD:$AD,lr)
Name Quantity
Refers to =$X$2:INDEX($X:$X,lr)

Then use
{=SUM((Customer=$C$3)*(Date="Dec 08")*Quantity)}

or the non-array entered

=SUMPRODUCT((Customer=$C$3)*(Date="Dec 08")*Quantity)

 

[winnie123]

Thank you Roger, Dave

I changed some of the names and had to add an extra ( before qty and it
works great.

{=SUM((Part=$C$3)*(Month="Dec 08")*(Qty))}

Thanks ever so much
Winnie

 

[Ashish Mathur]

Hi,

To make a range *auto expanding*, you may convert it from a range to a List
(Ctrl+L). Once you do this, the range in the formula will automatically
keep expanding for data appended beyond row 114.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Ashish Mathur]

Please note that this will work for versions beyond Excel 2003.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com