Excel SUM with VLOOKUP
Hi,
I need to sum some 30+ products like in the image below.
Basically look for the code in the lookup table, take the value, multiply it with the number in the next column. Then take all results and get the sum.
I can do it the long way, but the result is going to be a very long formula that's hard to maintain.
How can I do it with a shorter formula?
Thank you.
I need to sum some 30+ products like in the image below.
Basically look for the code in the lookup table, take the value, multiply it with the number in the next column. Then take all results and get the sum.
I can do it the long way, but the result is going to be a very long formula that's hard to maintain.
How can I do it with a shorter formula?
Thank you.
ASKER
Sorry, I did not realize that people actually use the mobile interface :)
The document from the image is attached.
You'll find in I1 the current formula that works but it's too long.
Q29058298.xlsx
The document from the image is attached.
You'll find in I1 the current formula that works but it's too long.
Q29058298.xlsx
Think that you probably already have the "shorter" answer using vlookup.
another way to do it is using:
but don't see it's "shorter".
another way to do it is using:
=(INDEX(B8:B15,MATCH(A1,A8:A15,0))*B1)+(INDEX(B8:B15,MATCH(C1,A8:A15,0))*D1)+(INDEX(B8:B15,MATCH(E1,A8:A15,0))*F1)but don't see it's "shorter".
ASKER
Nope, it's not shorter. In the original script I have to multiply from A1*B1 to BJ1*BK1, so the resulting formula it's going to be huuuge.
I think this is something that should be solvable using array formulas, but it's beyond my skill with Excel.
I think this is something that should be solvable using array formulas, but it's beyond my skill with Excel.
If that number will always be 8 then you don't need to multiply every cell, try below:
=SUMPRODUCT(((A8:A15=A1)+(A8:A15=C1)+(A8:A15=E1)),B8:B15)*8
Cool formula Shums, and if multiplier (8) of each product is defined within the data then formula can be written this way =SUMPRODUCT(((A8:A15=A1)+(
ASKER
Sadly, it's not always 8. It's the number of hours worked and the codes are the hourly rate.
If you are open to a VBA solution, you may derive your own custom function to get the desired output which will be much shorter and easier to use on the worksheet.
You may try something like this...
You may try something like this...
Function GetSumProduct(lookup_Arr As Range, lookup_table As Range) As Double
Dim x, xx, dict
Dim i As Long
Dim Total As Double
x = lookup_Arr.Value
xx = lookup_table.Value
Set dict = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(x, 2)
If Application.IsText(x(1, i)) Then
dict.Item(x(1, i)) = x(1, i + 1)
End If
Next i
For i = 1 To UBound(xx, 1)
If dict.exists(xx(i, 1)) Then
Total = Total + dict.Item(xx(i, 1)) * xx(i, 2)
End If
Next i
GetSumProduct = Total
End Function=GetSumProduct(A1:F1,A8:B15)
Try below Array Formula confirmed with Ctrl+Shift+Enter:
=SUMPRODUCT((($A$8:$A$15=$A$1)+($A$8:$A$15=$C$1)+($A$8:$A$15=$E$1)),$B$8:$B$15)*(SUM(IF(ISNUMBER($A$1:F$1),$A$1:F$1)))/SUMPRODUCT(--NOT(ISNUMBER($A$1:F$1)))SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry, I had a million things to do and totally forgot about this.
A VBA solution is fine. But I tried it on the full file and it does not work. Can't figure out why.
The file is attached. The constants are on sheet 2.
Q29058298_VBA.xlsm
A VBA solution is fine. But I tried it on the full file and it does not work. Can't figure out why.
The file is attached. The constants are on sheet 2.
Q29058298_VBA.xlsm
Hi Dan,
It requires a bit different approach. I have tweaked the code so please refer to the attached and see if you are getting the desired output now.
It requires a bit different approach. I have tweaked the code so please refer to the attached and see if you are getting the desired output now.
Function GetSumProduct(lookup_Arr As Range, lookup_table As Range) As Double
Dim x, xx, dict, dict2, it
Dim i As Long
Dim Total As Double
x = lookup_Arr.Value
xx = lookup_table.Value
Set dict = CreateObject("Scripting.Dictionary")
Set dict2 = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(x, 2)
If Application.IsText(x(1, i)) Then
dict.Item(x(1, i)) = x(1, i + 1)
End If
Next i
For i = 1 To UBound(xx, 1)
dict2.Item(xx(i, 1)) = xx(i, 2)
Next i
For i = 1 To UBound(x, 2)
If dict2.exists(x(1, i)) Then
Total = Total + (dict.Item(x(1, i)) * dict2.Item(x(1, i)))
End If
Next i
GetSumProduct = Total
End FunctionASKER
It's mostly correct, but not on all rows.
Look at row 7. There are 22 days worked, each with 8 hours, each hour paid at 12.5. The result should be 2200, not 1500.
Look at row 7. There are 22 days worked, each with 8 hours, each hour paid at 12.5. The result should be 2200, not 1500.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you!
You're welcome Dan!
Is it possible to supply sample with your current formula plus expected result.,its difficult to understand.