# Comparing and then multiplying several cells

#### erik.p

##### New Member
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

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

##### Well-known Member
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

##### New Member
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

##### Well-known Member
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

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

##### Well-known Member
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

##### New Member
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!

Replies
0
Views
98
Replies
0
Views
186
Replies
3
Views
397
Replies
2
Views
174
Replies
1
Views
102

1,195,919
Messages
6,012,301
Members
441,690
Latest member
CyberWrek

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back