# Use SUMIF with VLOOKUP or any other way

#### k_gitnos

##### New Member
Hello everyone,

I have been struggling to find a more automated way in order to make a calculation the past few months and I would like your help. I will give you a more detailed description of what I want to achieve.

Table 1 is in file Filename001.xlsx and Sheet1
 A B C 1 X1 Y1 Z1 2 X1 Y2 Z2 3 X2 K1 Z3 4 X3 M1 Z4 5 X3 M2 Z5 6 X2 K2 Z6 7 X1 Y3 Z7 8 X2 K3 Z8 9 X1 Y4 Z9

<tbody>
</tbody>

Table 2 is in file Filename002.xlsx and Sheet2
 A B 1 Y1 R1 2 Y2 R2 3 Y3 R3 4 Y4 R4

<tbody>
</tbody>

Table 3 is in file Filename002.xlsx and Sheet3
 A 1 X1 2 X2 3 X3 4 X4

<tbody>
</tbody>

I write the following code in file Filename001 and Sheet1

=(C1+C2+C7+C9)/((C1/(VLOOKUP(B1,[Filename002.xlsx]Sheet2!\$A\$1:\$Z\$4,2,FALSE)))+(C2/(VLOOKUP(B2,[Filename002.xlsx]Sheet2!\$A\$1:\$Z\$4,2,FALSE)))+(C7/(VLOOKUP(B7,[Filename002.xlsx]Sheet2!\$A\$1:\$Z\$4,2,FALSE)))+(C9/(VLOOKUP(B9,[Filename002.xlsx]Sheet2!\$A\$1:\$Z\$4,2,FALSE))))

Basically I locate X1 manually each time. What I want to do is to replace the above code with an automated one. So far, the only thing I found a way to replace was

(C1+C2+C7+C9)
with
(SUMIF(\$A1:\$A9,[Filename002.xlsx]Sheet3!\$A\$1,\$C1:\$C9))

Is it possible what I'm trying to do over here?

Any ideas are welcomed. Thank you in advance for your time.

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

#### CyrusTheVirus

##### Well-known Member
what are you trying to sum? I don't see any numbers.

#### k_gitnos

##### New Member
I will re-write my post and I will be more specific of what I try to do

In Sheet1 I have the following table
 A B C 1 Fruit Apple 4 2 Fruit Cherry 6 3 Car BMW 8 4 Car Mercedes 1 5 Vegetable Cabbage 2 6 Car VW 4 7 Fruit Orange 6 8 Car Audi 4 9 Fruit Pear 3

<tbody>
</tbody>

In Sheet2,
 A B C 1 VW 12 50 2 BMW 65 60 3 Mercedes 50 90 4 Audi 32 100

<tbody>
</tbody>

In Sheet3,
 A B C 1 Carrot 4 15 2 Lettuce 6 30 3 Cabbage 2 30 4 Potato 9 15

<tbody>
</tbody>

In Sheet4,
 A 1 Fruit 2 Car 3 Vegetable

<tbody>
</tbody>

In Sheet5,
 A B C 1 Orange 15 40 2 Cherry 18 30 3 Apple 12 60 4 Pear 4 80

<tbody>
</tbody>

In Sheet1, the columns A and B are drop-down lists which mean I can change their content according to my needs.
In a cell I want to calculate the following fraction

Σxi/Σ(xi/yi)

and I use the following code altering it manually each time

=(C1+C2+C7+C9)/((C1/VLOOKUP(B1,Sheet5!A1:C4,2,FALSE))+(C2/VLOOKUP(B2,Sheet5!A1:C4,2,FALSE))+(C7/VLOOKUP(B7,Sheet5!A1:C4,2,FALSE))+(C9/VLOOKUP(B9,Sheet5!A1:C4,2,FALSE)))

What I want to do is to have a line of code which calculates everything automatically whenever there is "Fruit" in column A. For example, instead of writing (C1+C2+C7+C9), we can write SUMIF(A1:A9,Sheet4!A1,C1:C9). The problem is I don't know what kind of code I can use for the second part. Obviously, an array will be used but I have no idea how to get there.

Any help is welcomed.

#### DanteAmor

##### Well-known Member
Try the following

Excel Workbook
ABCDEFGH
1ConceptDescriptionValueConceptSheetFruit ValueResult
2FruitApple4FruitSheet50.3310.46
3FruitCherry60.33
4CarBMW80.00
5CarMercedes10.00
6VegetableCabbage20.00
7CarVW40.00
8FruitOrange60.40
9CarAudi40.00
10FruitPear30.75
Sheet1

Excel Workbook
AB
1ConceptSheet
2FruitSheet5
3CarSheet2
4VegetableSheet3
Sheet4

Excel Workbook
ABC
1FruitValue
2Orange1540
3Cherry1830
4Apple1260
5Pear480
Sheet5

#### k_gitnos

##### New Member
Thank you Dante Armor for your effort, but I think I am misunderstood.

The data in Sheet2, Sheet3, Sheet4, Sheet5 are fixed. Consider these sheets as databases and I don't move them around. Worse case scenario, they are in a different file.

In Sheet1 as I mentioned, all cells in column A have drop-down lists. For example, later I may replace "Car" in A4 with "Fruit" and "Mercedes" and I want the calculation to be done in a single cell automatically. Without altering now the line to

=(C1+C2+C4+C7+C9)/((C1/VLOOKUP(B1,Sheet5!A1:C4,2,FALSE))+(C2/VLOOKUP(B2,Sheet5!A1:C4,2,FALSE))+(C4/VLOOKUP(B4,Sheet5!A1:C4,2,FALSE))+(C7/VLOOKUP(B7,Sheet5!A1:C4,2,FALSE))+(C9/VLOOKUP(B9,Sheet5!A1:C4,2,FALSE)))

#### DanteAmor

##### Well-known Member
I understand, in your example you want a return of 4 results "Apple", "Cherry", "Orange" and "Pear", then take those 4 results and look them up in the sheet5 to get values.
I keep checking if it is possible that a formula returns several results.

The other option would be a macro.

#### k_gitnos

##### New Member
In the past, when I had all my data in one sheet and not spread like now, someone in another forum gave me the following solution

=SUMIF(\$A\$1:\$A\$9,"Fruit",\$C\$1:\$C\$9)/SUM(IF(\$A\$1:\$A\$9="Fruit",\$C\$1:\$C\$9/VLOOKUP(T(IF({1},\$B\$1:\$B\$9)),Sheet6!\$A\$1:\$C\$14,2,FALSE)))

whereas Sheet6 has all data from Sheet2, Sheet3 and Sheet5. But now it results an error because when VLOOKUP kicks in, it cannot find the information for the other information (Car and Vegetable) in order to calculate the fraction and at the end to ignore. What I have understood is that Excel calculates the fractions first and then it eliminates everything that it does not belong to "Fruit" instead of doing the other way around.

This may give you a hint.

#### k_gitnos

##### New Member
I corrected the mistake in the line. My quest is over.

=SUMIF(\$A\$1:\$A\$9,Sheet4!\$A\$1,\$C\$1:\$C\$9)/SUM(IFERROR((IF(\$A\$1:\$A\$9=Sheet4!\$A\$1,\$C\$1:\$C\$9/VLOOKUP(T(IF({1},\$B\$1:\$B\$9)),Sheet5!\$A\$1:\$C\$14,2,FALSE)),"")))

and instead of pressing ENTER, you press CTRL+SHIFT+ENTER

Perfect!

#### DanteAmor

##### Well-known Member
I tried the formula and I had problems with a parenthesis, I put the formula with the change in case you need it.

=SUMIF(\$A\$1:\$A\$9,Sheet4!\$A\$1,\$C\$1:\$C\$9)/SUM(IFERROR((IF(\$A\$1:\$A\$9=Sheet4!\$A\$1,\$C\$1:\$C\$9/VLOOKUP(T(IF({1},\$B\$1:\$B\$9)),Sheet5!\$A\$1:\$C\$14,2,FALSE))),""))

Replies
0
Views
565
Replies
2
Views
165
Replies
2
Views
170
Replies
0
Views
276
Replies
13
Views
506

1,185,991
Messages
5,955,189
Members
438,186
Latest member
subvtech

### 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.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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