Use SUMIF with VLOOKUP or any other way

k_gitnos

New Member
Joined
Mar 2, 2017
Messages
14
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
ABC
1X1Y1Z1
2X1Y2Z2
3X2K1Z3
4X3M1Z4
5X3M2Z5
6X2K2Z6
7X1Y3Z7
8X2K3Z8
9X1Y4Z9

<tbody>
</tbody>

Table 2 is in file Filename002.xlsx and Sheet2
AB
1Y1R1
2Y2R2
3Y3R3
4Y4R4

<tbody>
</tbody>

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

<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

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

CyrusTheVirus

Well-known Member
Joined
Jan 28, 2015
Messages
737
Office Version
  1. 365
Platform
  1. Windows
what are you trying to sum? I don't see any numbers.
 

k_gitnos

New Member
Joined
Mar 2, 2017
Messages
14
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
ABC
1FruitApple4
2FruitCherry6
3CarBMW8
4CarMercedes1
5VegetableCabbage2
6CarVW4
7FruitOrange6
8CarAudi4
9FruitPear3

<tbody>
</tbody>


In Sheet2,
ABC
1VW1250
2BMW6560
3Mercedes5090
4Audi32100

<tbody>
</tbody>


In Sheet3,
ABC
1Carrot415
2Lettuce630
3Cabbage230
4Potato915

<tbody>
</tbody>

In Sheet4,
A
1Fruit
2Car
3Vegetable

<tbody>
</tbody>

In Sheet5,
ABC
1Orange1540
2Cherry1830
3Apple1260
4Pear480

<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
Joined
Dec 3, 2018
Messages
14,155
Office Version
  1. 2007
Platform
  1. Windows
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
Joined
Mar 2, 2017
Messages
14

ADVERTISEMENT

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
Joined
Dec 3, 2018
Messages
14,155
Office Version
  1. 2007
Platform
  1. Windows
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
Joined
Mar 2, 2017
Messages
14

ADVERTISEMENT

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
Joined
Mar 2, 2017
Messages
14
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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
14,155
Office Version
  1. 2007
Platform
  1. Windows
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))),""))
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,579
Messages
5,765,208
Members
425,265
Latest member
bishopc22

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?

Disable AdBlock

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
Top