Subtotaling one sheet on another sheet

[Brenda from Michigan]

Excel 2003 SP3

It's been years since I've had training and this one is just getting away
from me. User has Sheet1 with list of material types and material amounts.
This is setup as a form so neither of these columns is the leftmost column.

He wants to summarize a total of Material A, Material B, etc. on Sheet 2 by
adding the respective amounts of each material in Sheet 1 and showing the
total on Sheet 2. If I could get him to simply copy sheet 1 to sheet 3, then
sort and sub-total, it would be easy, but that's not what he wants. Can
anyone offer any advice? Thanks!

 

[Eduardo]

Hi Brenda,
I don't understand why you need to summarize in sheet 1 information from
sheet2 and then show the results in sheet2 but anyway try this
Let's assume that the material code is in column A sheet2 and amounts in
column B
in sheet 1 column A make a list of your materials starting in row 2 and in
B2 enter this formula

=SUMPRODUCT(--(A2=Sheet2!A2:A13),--Sheet2!B2:B13)

Change the range to fit your needs but remember always the range has to be
the same in both parts of the formula
Then in sheet 2 you can pull the information from sheet1
If this was helpful please say yes. thank you

 

[Brenda from Michigan]

I was not clear; please let me rephrase.

In sheet 1 he may have 50 entries for materials and a total of 5 different
types of materials. i.e. Mat1, MatA, Mat34, Mat5, etc.

He wants to pull out all the Mat1 on sheet one, add its appropriate total
(there may be 5-10 entries for Mat1 spread throughout the column), and show
the total in a Summary on sheet 2.

Then you do the same for MatA, Mat34, Mat5, and so on. I keep thinking this
is a LOOKUP or a Consolidating function, but I can't get my mind wrapped
around how to set it up.

 

[Eduardo]

Hi Brenda,
I answer but it looks like it didn't go through, Ok information is in sheet
1 and summary in sheet2
I am assuming that in sheet1 column A you have the material names and in
column B the information to be summarize, change it to suite your needs. In
sheet 2 column A make a list of your materials i.e. cell A2=Mat 1; Cell
A3=Mat 2
In cell B2 enter the formula as follow and copy it all the way down

=SUMPRODUCT(--(A2=Sheet1!$A$2:$A$100),--Sheet1!$B$2:$B$100)
If you are using excel 2007 use

=SUMPRODUCT(--(A2=Sheet1!A:A),--Sheet1!B:B)
Hope this help