VLOOKUP won't work within SUMPRODUCT

[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.:

 

[Frank Kabel]

Hi
one simple solution would be to add a helper column to your source data
sheet with the region name (using a VLOOKUP formula).
After this use this helper column in your SUMPRODUCT formula

 

[bill_s1416]

Frank,
Thanks for your reply. Yes, I know that is one way to do it. However
what I didn't point out (for fear of making my original question to
long-winded) is that there are MANY data sheets that are being pulle
from to get the regional sales. So I didn't want to have to go int
all the data sheets and create a region helper column (and also all th
data sheets get updated each month.)
Thank you anyway.:

 

[Bob Phillips]

Bill,

It worked fine for me/

I created a range DistToReg with this

CO West
NE East


and a Territory, Product and Sales range of this

CO123 A 1
CO789 B 2
NE123 C 3
CO123 A 7


and West in A2, A in B2, and I got 8 as expected.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Harlan Grove]

There is a table that cross-references the territory's district (1st
two characters of the TerritoryNumber to the region--either "East" or
"West.") Example:
_District_ _Region_
CO West
NE East
PA West

Table layout is a pain in newsreaders. Usually safest to use underscores
rather than spaces or tabs.

If the territory number is -CO01- then the region is -West-.

I created a blank regional sales worksheet with three columns (headings
in row 1) to sum the sales: Region, Product, Sales. There are six rows
since there are two regions ("East" and "West") and three products
("A", "B" and "C".) Therefore, in cell C2 I entered a sumproduct
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 for
that product, when it should be just for the region in cell A2.

My results are different, always zero. Bob's were different still - he got a
correct answer. This makes me suspect that feeding VLOOKUP an array first
argument results in one Excel's screwball, undocumented worksheet objects.
That is, I suspect the return value from VLOOKUP is something that's subject
to implicit indexing, so the result you see in any cell depends on the cell
into which you enter the formula.

I never use {H|V}LOOKUP with array first arguments. I've had too many
problems with them, but I'd never thought about causes before.

Anyway, when you need to ensure an array result, use LOOKUP. This requires
that you sort DistToReg by its first column in ascending order. Try

=SUMPRODUCT((LOOKUP(LEFT(Territory,2),INDEX(DistToReg,0,1),
INDEX(DistToReg,0,2))=A2)*(Product=B2),Sales)

If DistToReg actually spans two columns but more than two rows, you could
get away with

=SUMPRODUCT((LOOKUP(LEFT(Territory,2),DistToReg)=A2)*(Product=B2),Sales)

 

[bill_s1416]

Harlan,
Your final formula worked exactly on my spreadsheet as it did on yours
The problem is when I modified it slightly and put it in my "real
workbook it evaluates to #N/A. When I highlight the individua
sections of your formula in my test worksheet and hit F9, the array
{TRUE, FALSE,TRUE} etc. appear correctly in the formula bar. When
try to do that however, in my real workbook it keeps popping up th
"formula is too long" box.

I am ready to take a sledgehammer to my computer.:mad

 

[Harlan Grove]

Your final formula worked exactly on my spreadsheet as it did on yours.
The problem is when I modified it slightly and put it in my "real"
workbook it evaluates to #N/A. When I highlight the individual
sections of your formula in my test worksheet and hit F9, the arrays
{TRUE, FALSE,TRUE} etc. appear correctly in the formula bar. When I
try to do that however, in my real workbook it keeps popping up the
"formula is too long" box.

....

Show your *exact* formula that's evaluating #N/A.

 

[bill_s1416]

Harlan,
I figured out the glitch. YAHOOOOO!!!!!

_Public_service_warning:_ When using LOOKUP, the values in the array i
references *MUST BE IN ASCENDING ORDER!* That's why it kept returnin
#N/A. In my reference table (District--Region) it was in region order
not district order. (Of course the Excel help file, while mentionin
the ascending requirement, does not mention the #N/A part so I neve
picked up on it.)

Anyway, I'm as happy as a clam at high tide! (-Troy McLain, formerly o
-The Apprentice.-)

Harlan, thank you for your excellent LOOKUP formula. You saved me fro
having to go into each data sheet every month and create "region
columns. :

 

[Harlan Grove]

I figured out the glitch. YAHOOOOO!!!!!

....

From my original response: "Anyway, when you need to ensure an array result,
use LOOKUP. This requires that you sort DistToReg by its first column in
ascending order."