Using VLOOKUP and SUM to calculate data from different sheets

Neuron

New Member
Hi all,

I am trying to find a way to calculate a value using parameters stored in different sheets/tables. I have been trying to do this with sum and vlookup without success. The input data looks like this:

 Type Power A 5 A 9 B 0 B 1 C 6 C 2 C 3 D 7 D 0 D 0

<tbody>
</tbody>

and

 Type Base A 3 B 3 C 4 D 5

<tbody>
</tbody>

For each line in the first table I would like to calculate

=IF(Power>0;VLOOKUP(Table1!Type;Table2;2;FALSE)*2^Table1!Power;0)

I am able to calculate this for each line, but I need to calculate the sum of this value for all the lines in a single cell.

All my attempts with VLOOKUP have so far met with failure. Any help is greatly appreciated!!

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

CyrusTheVirus

Well-known Member
Hi Neuron,

Welcome to the forums.

Which table above is Table 1 and which is Table 2?

What exactly are you trying to do? In other words, what is your expected answer?

And could you please clarify what you mean by the below?

I am able to calculate this for each line, but I need to calculate the sum of this value for all the lines in a single cell.

Neuron

New Member
Sorry I'll try to explain better.

 Type (A) Power (B) CalcValue (C) (D) Type (E) Base (F) A 5 3 A 3 A 9 3 B 6 B 0 0 C 5 B 1 6 D 4 C 6 5 C 2 0 C 3 0 D 7 0 D 0 4 D 0 4

<tbody>
</tbody>

The calculation formula for C2 is : =IF(B2>0;VLOOKUP(A2;\$E\$2:\$F\$5;2;FALSE);0)

Since I have many columns like B, each of which has a different set of Powers for the "Types" in A column, I would like to calculate the sum of the C column in one formula in one cell (fx. in "B12"), and not have to add the C column to my sheet.

I hope that explains it...

Neuron

New Member
Sorry wrong formula .. this should be correct:

 Type (A) Power (B) CalcValue (C) (D) Type (E) Base (F) A 5 24 A 3 A 9 12 B 6 B 0 0 C 5 B 1 12 D 4 C 6 80 C 2 0 C 3 0 D 7 0 D 0 256 D 0 1024

<tbody>
</tbody>

The calculation formula for C2 is : =IF(B2>0;VLOOKUP(A2;\$E\$2:\$F\$5;2;FALSE)*2^B2;0)

CyrusTheVirus

Well-known Member
Can you please explain how 3*2^5 = 24 ?

And how is C11 = 1,024? I thought you wanted C11 to be 0 because your IF statement says if B11 > 0 then 0. But, then why is it 1,024?

Neuron

New Member
I guess it shows how tired I was when I posted... numbers in column B should be as follows:

3,2,0,1,4,0,0,0,6,8

Neuron

New Member
I am deeply sorry for the messed up posting above and appreciate your interest and help. I have worked on this a little bit more ..

 Type (A) Power (B) CalcValue (C) (D) Type (E) Base (F) A 3 24 A 3 A 2 12 B 6 B 0 0 C 5 B 1 12 D 4 C 4 80 C 0 0 C 0 0 D 0 0 D 6 256 D 8 1024

<tbody>
</tbody>

The single-cell array formula for C2:C11 is: {
=IF(B2:B11>0;VLOOKUP(A2:A11;\$E\$2:\$F\$5;2;FALSE)*2^B2:B11;0)}.

The sum of the values in C2:C11 is 1408.

The multi-cell array formula: {=SUM(IF(B2:B11>0;VLOOKUP(A2:A11;\$E\$2:\$F\$5;2;FALSE)*2^B2:B11;0))} and returns 1050.

I would have expected the multi-cell array formula to return 1408.

Neuron

New Member
It seems the multi-cell array formula assumes I always have type A.

CyrusTheVirus

Well-known Member
Hi Neuron,

You were almost there. Please keep in mind that the lookup_value argument in the VLOOKUP function cannot handle an array of values, but the criteria argument of the SUMIF/SUMIFS functions can handle an array of values.

Try out the below. Enter the array formula in F2 with Ctrl+Shift+Enter, not just Enter.

Excel Workbook
ABCDEF
1TypePower**Array1408
2A3****
3A2**TypeBase
4B0**A3
5B1**B6
6C4**C5
7C0**D4
8C0****
9D0****
10D6****
11D8****
Sheet6

Last edited:

Neuron

New Member
You sir, are a genius!

Thank you so very much for the help, it will enable me to get rid of some very heavy vba custom functions.

1,095,234
Messages
5,443,271
Members
405,222
Latest member
Summer01

This Week's Hot Topics

• Copy entire row if CountA <>0 to another sheet
[B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
• Select last used Row in Table
I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
• excel workbook: do not allow certain file name
Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
• fixing problem autofilter
hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
• “Weight”
Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
• How to capitalize everything before a certain character?
In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...