Lookup one line down

[Spencer]

I have a listing of sales data for different regions for each month that has
the item running down column A. In column B I have the gross sells and in the
cell directly below that I have the quantity sold. As such

Region Product Data Sep Oct
Nov
North Widgets Gross Sales 100 120 110
Quantity 20
30 25
Trinkets Gross Sales 500 480
510
Quantity 100 80
105

And so it repeats with every region showing all the products we sell and the
sales for that region.

I am able to calculate the gross sales for each product using
=sumproduct((Product=Widgets)*(Data=Gross Sales)*Sep and I get the right
numbers. However, I don't know how to calculate the Quantity since it doesn't
have the name Widget (or whatever the product may be) in the same row.

How can I calculate the quantity sold for each product for all regions?

Thanks in advance.

 

[Ron Coderre]

With your posted data (repeated 4 times for regions: North, South, East,
West) in A1:F17

and...
I1: Product
I2: Widgets

J1: Data
J2: Gross Sales

K1: Sep

This formula returns the total Gross Sales for Widgets:
K2: =SUMPRODUCT(($B$2:$B$17=$I2)*($D$1:$F$1=K$1)*$D$2:$F$17)

and this formula returns the total Quantity for Widgets:
K3: =SUMPRODUCT(($B$2:$B$17=$I3)*($D$1:$F$1=K$1)*$D$3:$F$18)
Note: That formula calcs beginning in Row_3 (versus Row_2, above).

Copy those formulas down for other Product/Data in Col_I and Col_J.

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Herbert Seidenberg]

I assume your data looks like this:
(with all the tab and 0160 characters removed)
Data Product Sep Oct Nov
Gsales Widgets 100 120 110
Quantity 20 30 25
Gsales Trinkets 500 480 510
Quantity 100 80 105
The top row shows the named ranges.
=SUMPRODUCT((Product="Widgets")*(Data="Gsales")*Sep) =100
but
=SUMPRODUCT((Product="Widgets")*(Data="Quantity")*Sep)
is 0 instead of 20 because Widgets is not repeated in the Quantity
row.
A quick solution is to place the name Prod above Product and use it
to define the next 4 cells down (not 5 cells since all Sumproduct
ranges
have to have the same length).
This in effect shifts the Product entries down one row.
The formula for quantity then becomes:
=SUMPRODUCT((Prod="Widgets")*(Data="Quantity")*Sep) =20