B
bill_s1416
Desired goal: To sum the sales by *product* by *region*.
The source data worksheet has 3 columns: TerritoryNumber, Product
Sales. (note *no* region indicator.)
How to get the region indicator?
There is a table that cross-references the territory's district (1s
two characters of the TerritoryNumber to the region--either "East" o
"West.") Example:
_District_ _Region_
CO West
NE East
PA West
If the territory number is -CO01- then the region is -West-.
I created a blank regional sales worksheet with three columns (heading
in row 1) to sum the sales: Region, Product, Sales. There are six row
since there are two regions ("East" and "West") and three product
("A", "B" and "C".) Therefore, in cell C2 I entered a sumproduc
formula:
=SUMPRODUCT((VLOOKUP(LEFT(Territory,2),DistToReg,2,FALSE)=A2)*(Product=B2)*(Sales))
Where -DistToReg- is the cross-reference table.
The formula is not working because it sums *both* regions' sales fo
that product, when it should be just for the region in cell A2.:
The source data worksheet has 3 columns: TerritoryNumber, Product
Sales. (note *no* region indicator.)
How to get the region indicator?
There is a table that cross-references the territory's district (1s
two characters of the TerritoryNumber to the region--either "East" o
"West.") Example:
_District_ _Region_
CO West
NE East
PA West
If the territory number is -CO01- then the region is -West-.
I created a blank regional sales worksheet with three columns (heading
in row 1) to sum the sales: Region, Product, Sales. There are six row
since there are two regions ("East" and "West") and three product
("A", "B" and "C".) Therefore, in cell C2 I entered a sumproduc
formula:
=SUMPRODUCT((VLOOKUP(LEFT(Territory,2),DistToReg,2,FALSE)=A2)*(Product=B2)*(Sales))
Where -DistToReg- is the cross-reference table.
The formula is not working because it sums *both* regions' sales fo
that product, when it should be just for the region in cell A2.: