summing of numbers between sheets for same column values

[joe.demauro]

I am trying to figure out how to add the number of items placed in a
system. Each system has multiple items in it with different yearly
quantities.

For an example, in sheet1 I have a table with 3 columns A- is the item#
(00010001), B- is the system that Item # is placed in (BB001), C- is
the quantity of items in that given system (111000). In sheet 2 I would
like column A to be each system (BB001-BB100) and column B to be the
sum of all of the different items #'s in that system (lets say system
BB001 has 3 item #'s 00010001, 00010002 and 00010003, all three of them
have a quantity of 111000).

What function or formula would give me the correct sum of 333000 for
system BB001? How would I make this dynamic so that if 2 more items
were added to system BB001 their quantities would be included in the
overall sum of quantities in that system?

 

[Harlan Grove]

(e-mail address removed) wrote...
....

For an example, in sheet1 I have a table with 3 columns A- is the item#
(00010001), B- is the system that Item # is placed in (BB001), C- is
the quantity of items in that given system (111000). In sheet 2 I would
like column A to be each system (BB001-BB100) and column B to be the
sum of all of the different items #'s in that system (lets say system
BB001 has 3 item #'s 00010001, 00010002 and 00010003, all three of them
have a quantity of 111000).

....

If you mean that sheet2 column B should have the sums of the quantities
from sheet1 column C for corresponding systems, try this formula.

=SUMPRODUCT(SUMIF(sheet1!B:B,"BB"&TEXT(ROW(INDIRECT("1:100")),"000"),
sheet1!C:C))