Need a problem solving formular (two way Lookup?, match?)

[Solway]

i'll try and explain what i am after.

I need to set up a formula to automatically find and match certain values in a table, then added up a cell to produce an end total.

For example (see simple version of the table, Below Picture),

for one of the examples, matching a type '16' to the shape '00' and add up the 'weight' values (as manually shown in cell with the arrows). And ditto to the other patterns.

However with 7500 rows i need this automatic.

I did look at 'lookup' & 'index' formulas, however got confused and didn't think it would produce the right results, or i did it wrong?

I am using XP with Excel 2003, i did try and install that plugin for the board, however produced a runtime error.

If someone can help me, i would be extremely appreciated.
Once i get the main base of the formula i can adapt it.

Regards
Andrew

 

[Domenic]

You can use SUMPRODUCT...

=SUMPRODUCT(--(ShapeRange=Shape),--(TypeRange=Type),WeightRange)

Hope this helps!

 

[Solway]

Thank you very much!

one last question regarding this

is there a way to added up all the rest of the shapes that are not '00'

for example

=SUMPRODUCT(--(D1:D120=12),--(I1:I120=00),P1:P120) ---> gives me the shapes '00' ones



all the other shapes are random, like 21, 11, 51 etc..

so for picking up all the other shapes with a weight of 12, is there a simple way to do this?

=SUMPRODUCT(--(D$1:D$120=12),--(I$1:I$120=21,11,51),P$1:P$120)

??

 

[Solway]

update:

i have solved this with a new column next to the shape numbers.
Using IF command

=IF(I7=00,"Straight","Bent")


unless there is a more simple process, that can be incorporated in the previous SUMPRODUCT formula

 

[Domenic]

Try...

=SUMPRODUCT(--(D$1:D$120=12),--ISNUMBER(MATCH(I$1:I$120,{21,11,51},0)),P$1:P$120)

Hope this helps!

 

[lalbatros]

Solway,

It seems to me that you could benefit from using pivottables for these calculations.
No formula would be involved and it would be extrmely flexible.

You would also benefit from turning your table in an excel list (rigth-button, create list)

 

[Solway]

a big thank you for your help guys!
i will surly be coming back if i get stuck again.


i have chosen to use the first SUMPRODUCT formula and the IF formula as it gives a higher level of visualization when looking through the table.


i don't think pivottables or filters, would be an option, as the data needs to be viewed all at once, and set in stone for summarising even further.

i will post a screen shot of my final table tomorrow, Just for reference, if someone is searching for the same result.

thanks guys!
Andrew

 

[lalbatros]

Hi,

Here is how you can do that with a pivottable:

http://www.geocities.com/l.albatros/pictures/pivtab.xls

Pivottables are incredibely flexible and powerful.
You could make many different summary reports on this basis in a few seconds.