Yup - another question!

[Oggie Ben Doggie]

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.

 

[Biff]

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

 

[DOR]

Redefine your sheet1 names to cover only the numeric portion of the
ranges - exclude the headers. Then enter the following in B2 of
sheet2, assuming your headers are in row 1 and left labels in col A:

=SUMPRODUCT((Loc=$A2)*(Dept=B$1)*Quant)

Copy across and down.

HTH

 

[John Michl]

Personally, I'd use a pivot table for this. Your data is set up
appropriately and you have a lot of flexibility on what would be
included in the table. For instance, as new departments or locations
are added to the data, you could just refresh the pivot table and they
would be automatically added. Follow the pivot table wizard to get
started.

If, however, you want to manually create your second table, try this.

In Sheet2 assume the dept names start in cell B1 and work to the right.
Also assume the locations start in cell A2 and work down. In cell B2,
enter the following:

=SUMPRODUCT(--(Sheet1!$B$2:$B$1000=B$1),--(Sheet1!$A$2:$A$1000=$A2),Sheet1!$C$2:$C$1000)

Note the position of the dollar signs. This is critical to allow you
to copy this formula throughout your table without editing.

Good luck

- John Michl

 

[Oggie Ben Doggie]

The pivot table thing was a no-brainer - perfect! Dunno why I didn't
think of it... maybe it was lack of Excedrin... or just recognizing
what I was looking at!

Thanks!
OBD

 

[John Michl]

Glad to be able to help.

Considering that you probably will add data to this database, you might
consider creating a dynamic named range for the data area and use that
named range in the pivot table so that you don't have to revisit the
pivot table wizard to redefine the size of the database.

If that is new to you, here's how to get started.

From the menus...Insert > Name > Define

Name it Pivot_data or whatever is meaningful to you
Instead of selecting the area, enter the formula:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),3)

Here's what those formula components mean:

Sheet1!$A$1 - Use this point as a starting reference
0,0 - start the range zero rows and columns from the starting
reference. (if you didn't want the labels it would be 1,0 but you need
the labels for the pivot table)
COUNTA(Sheet1!$A:$A) - height of the range which happens to be equal to
the number of rows with data in column A. If there might be some
blanks, then choose another column to count
3 - the width or number of columns in the table

Hope that helps.

 

[Oggie Ben Doggie]

Thanks for the information!

So often, I get a reply that doesn't fully explain the why's.

Regards

O.B.D.