Comparing and then multiplying several cells

[erik.p]

My first post, please forgive me if this exact question has been asked 100 times before...

Here goes...

In my first Excel file I have the following information;

• 
  Car______Item______Amount
  --------------------------------
  Volvo______B___________4
  SAAB______C___________3
  JEEP_______B___________8
  Mercedes___A___________2

In my second Excel File I have the following information;

Amount of Volvo cars: 10
Amount of SAAB cars: 5
Amount of JEEP cars: 3
Amount of Mercedes cars: 2



In my third, and final, Excel file I want Excel to do the following;

Total sum of item A : (2*2) = 4
Total sum of item B : (4*10+3*8) = 64
Total sum of item C : (3*5) = 15

It would be great if it was possible for Excel to check one column for the item name ("A", "B") and then do the above mentioned calculation only when Excel finds this item name. This so that I don't have to manually adjust each formula and enter "A", "B" etc. for each and every Item I want the sum of.

I hope my question makes sense and many thanks in advance.

Regards,

Erik

 

[onlyadrafter]

Hello and welcome to the board,

When you say in one Excel file, do you mean the data is in different files, or is it just on different sheets?

I think this will be a SUMPRODUCT formula.

 

[erik.p]

Different files (*.xls), altough I guess I could smack them all together into one big Excel file, but I'd prefer to not, unless I have to...

I agree with you, it should be some kind of SUMPRODUCT, but I 've neen trying that all day without getting it right...


EDIT: Spelling

 

[onlyadrafter]

Hello,

THis works if the data is on one sheet

in G1 enter this formula and copy down

=SUMPRODUCT(($B$1:$B$4=F1)*($C$1:$C$4)*($D$1:$D$4))

where
A1:A4 = cars
B1:B4 = item
C1:C4 = Amount
D1:D4 = Amount of cars
F1:F3 = A, B and C

have a play with the formula if data in different files.

 

[erik.p]

Thanks! I'll give it a shot and post my success/failure here...

 

[onlyadrafter]

Hello,

Have come up with this

=SUMPRODUCT(([Book8.xls]Sheet1!$B$1:$B$4=A1)*([Book8.xls]Sheet1!$C$1:$C$4)*([Book7.xls]Sheet1!$B$1:$B$4))

change file names as required.

Book 8 is the volvo, B, 4 etc
Book 7 is the volvo, 10 etc

 

[erik.p]

Hi,

Thanks, I figured that bit out myself. I had to rearrange my figures a bit, but after much trial and error I finally nailed it!

Many thanks! I wouldn't have made it without you!