Propagate Array Formula Down Column

[jim.excel]

Hi,

My situation:

Sheet1

A B C D E

1 Qty>> 4 1 2 3
2
3 DNG2 5 5 5 5
4 DNG3 2 4 7 1
5 DNG4 6 7 9 4
6 DNG5 2 2 3 6
7 DNG6 8 5 6 6


Sheet2

Total

DNG2 50


The Array Formula for calculating the Total for DNG2 is
{=SUMPRODUCT(Sheet1!$B$1:$E$1,Sheet1!B3:E3)}

How do I create a formula to calculate the Total Quantities for DNG3
through DNG6? This is in actually a much bigger list so I
want to be able to copy the formula down the column on Sheet2 and
auto-reference the proper rows in Sheet1.

Note: the Item Numbers (DNG2, DNG3, etc.) are unique.

Thanks,
Jim

 

[Biff]

Hi!

Your formula:

{=SUMPRODUCT(Sheet1!$B$1:$E$1,Sheet1!B3:E3)}

Does not have to be entered as an array.

Assume you have on Sheet2 starting in A2 your item numbers:

A2 = DNG2
A3 = DNG3
A4 = DNG4
etc

In Sheet2 B2 enter this ARRAY formula:

=SUM(IF(Sheet1!A$3:A$7=A2,Sheet1!B$3:E$7*Sheet1!B$1:E$1))

Copy down as needed or just double click the fill handle.

Biff