Help with Array Formula
 

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!$A D$2&"_"&Row="Dec
08")*(openorders!$X$2&"_"&Row))

Unfortunately this gives me a #NAME error.

What have I done wrong.
Thanks
Winnie

Help with Array Formula
 

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

"winnie123" wrote:

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!$A D$2&"_"&Row="Dec
08")*(openorders!$X$2&"_"&Row))

Unfortunately this gives me a #NAME error.

What have I done wrong.
Thanks
Winnie

Help with Array Formula
 

Hi Winnie

Create some Dynamic named ranges
InsertNameDefine
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)



--
Regards
Roger Govier

"winnie123" wrote in message
...
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!$A D$2&"_"&Row="Dec
08")*(openorders!$X$2&"_"&Row))

Unfortunately this gives me a #NAME error.

What have I done wrong.
Thanks
Winnie

Help with Array Formula
 

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


"Roger Govier" wrote:

Hi Winnie

Create some Dynamic named ranges
InsertNameDefine
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)



--
Regards
Roger Govier

"winnie123" wrote in message
...
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!$A D$2&"_"&Row="Dec
08")*(openorders!$X$2&"_"&Row))

Unfortunately this gives me a #NAME error.

What have I done wrong.
Thanks
Winnie

Help with Array Formula
 

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

"winnie123" wrote in message
...
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!$A D$2&"_"&Row="Dec
08")*(openorders!$X$2&"_"&Row))

Unfortunately this gives me a #NAME error.

What have I done wrong.
Thanks
Winnie

Help with Array Formula
 

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

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"winnie123" wrote in message
...
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!$A D$2&"_"&Row="Dec
08")*(openorders!$X$2&"_"&Row))

Unfortunately this gives me a #NAME error.

What have I done wrong.
Thanks
Winnie