Use SUMIF with VLOOKUP or any other way

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

Table 2 is in file Filename002.xlsx and Sheet2

A B
1 Y1 R1
2 Y2 R2
3 Y3 R3
4 Y4 R4

Table 3 is in file Filename002.xlsx and Sheet3

A
1 X1
2 X2
3 X3
4 X4

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.

Please attach your data sample in a workbook, with the expected results mocked up to show what we should be aiming for.

An existing formula is not always the best way to explain what you want, describing the requirement in words is usually clearer.

I will try to be more specific. Thanks jason.b75 though for your reply.

Basically, I want to calculate a fraction of the type Σxi/Σ(xi/yi)

All cells from column A and B have a drop-down list which means they can change according to my needs. But if for example I want to
replace in A3 the "Car" with "Fruit" and correct also B4, I don't want to go to E2 where I do all the calculations and add a respective
line of code for B4. I want it to take into consideration the change and recalculate the fraction.

Just like it occurs when someone uses SUMIF.

I hope I am clear enough.

Try this array formula (I've used the table in sheet 5 in place of the references to book2).

=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)),Sheet5!$A$1:$C$14,2,FALSE)))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

To be honest, this would never cross my mind. Thank you so much. It works!

Could you do me another favor, please? Can you explain the VLOOKUP part of the code? I haven't seen something similar before and I would like to understand it.

VLOOKUP(T(IF({1},...

This part.

Thank you so much!

Normally with vlookup, you can only enter a single cell reference / value as the criteria, if you enter a range or array, only the first criteria is used, the rest fall into a void.

The T(IF({1} part of the formula preserves the range / array so that you can do multiple lookups with 1 formula. I don't understand it fully, I just know that it works.

This was where the method first came to light, https://www.excelforum.com/excel-for...n-the-fly.html there is a bit of a discussion there as to how it functions.