Selcting data from table without repeating plus calc's

[QChris]

Hi

Really need some help with this!

I have a table where the qty are manually entered and the sections are
selected from a pull down menu. Col D, F, H and J are the same pull down
menues.


Col B Col D Col E Col F Col G Col H Col I Col J Col K
qty section length section length section length section length

5 A 2200 B 1800 B 2000 C 654
3 I 2200 B 1800 G 1587 C 254
7 A 2200 B 2100 B 2000 C 300
30 F 2200 B 1800 G 2000 C 254
etc.

I'm trying to look at the "section" data and where it finds a section name
it then finds the length in the column next to it. That combination (i.e.
section A at 2100mm long) is then multiplied by the appropriate "qty" value
in corresponding row in column B. I would like it to find all the same
criteria to give a total (i.e. all the "section A" at a length of 2100 in the
table. Each "new" length for each section shoul list automatically.

Col I Col J
SECTION 1
Total parts 51 (i've got this formula)
length qty
2100 32
569 10
235 3
234 6
etc.

I have manually enterred the 2100, 32 etc above to show the results
required. I am trying to learn the excel formula's (with a lot of good help
from this site) but any suggestion of macro's will lose me! I would copy
across these 2 columns to suit each section type I have.

Really, really appreciate help on this!

 

[Jacob Skaria]

In cell C2 enter the below formula
=SUM($B$2:B2)

If it is from cell C1
=SUM($B$1:B2)

 

[QChris]

Hi Jacob,

Realised my table format isn't that clear.

Thanks for helping, but that only adds up all the numbers in that range.

It needs to look for (e.g) "A" in the larger table, list the length which
appears in the right hand column and record this in the smaller table. I
realise I have written 2100 and not 2200 in the small table, my error.

It then needs to find all the sections denoted by "A" in the big table and
multiply that by the "qty" in the same row, but column B, then add all the
other data with those same parameters.

This is recorded in the smaller table next to the appropriate length. If it
then finds an "A" but a different length, it puts that in the next row but
again, multiplying it by the figure under "qty" in row B and adding all the
same data. etc etc.

The second table I wrote is further down the page summarises the layout for
"A" section. This can be copied across for all the other section types. I
also realise I titled it SECTION 1 instead of section A, sorry. The figures
entered here is what I expect the results to look like.

Note when I mean section, this is lengths of metal, in different sizes (i.e.
40mm x 40 x 3mm box).

Hope this helps explain it?