Hi!

You'd have to change the range of your defined names to be something less
than the entire column. The Sumproduct function will not accept entire
columns as range arguments.

Then the formula would be:

=SUMPRODUCT(--(Loc=$A2),--(Dept=B$1),Quant)

Copied across then down.

Biff

"Oggie Ben Doggie" wrote in message
oups.com...
Hi again folks,

I have a spreadsheet with two worksheets. On the first worksheet I
have data like this (there should be tabs in the data - if you want to
play with it I think you can copy it to a spreadsheet):

Location Dept Qty
A groc -38.645
A groc -7
A froz -429
B groc -50
B groc -25
B vits -20
B vits -12
B froz -50
C cheese -159.541
C cheese -2
D groc -70
D vits -26
D cheese -81
D cheese -67
D cheese -2
D cheese -88
E vits -418.612
E vits -7
E vits -1435
E cheese -64
E books -328
E books -85
E books -110
E books -43


On the second worksheet I have information like this:

depts
groc vits froz cheese books
A sum of quant where (dept = groc and loc = a) should come out to:
-45.645
B
C
D
E

I don't know if you can help me out with this one. I've defined
certain columns - loc is the first worksheet A:A, dept is the first
worksheet B:B, and quant is the first worksheet C:C.

I'd like to be able to show in the second table of worksheet 2, a cross
matched table - location A under the groc column will show the sum of
all groc items for location A. The vits column for the row relating to
location A shows the sum of all items from worksheet 1 where the
location is equal to A and the dept is vits.

I'm sorry if this is a little choppy - I have the logic in my head, and
I can't get the excel formula to come out.

Help?

O.B.D.