Simply an Existing Formula

meppwc

Well-known Member
Joined
May 16, 2003
Messages
604
Office Version
  1. 365
Platform
  1. Windows
Looking at the formula below you see a total of 3 arguments
=(VLOOKUP(G33,PivTbl_LookUp!A:B,2,FALSE)+(VLOOKUP(H33,PivTbl_LookUp!A:B,2,FALSE)+VLOOKUP(I33,PivTbl_LookUp!A:B,2,FALSE)))
I am hoping there is a way of simplifying it (shortening it) because I will have a total of 18 arguments (it will count column G through X)
My example includes only G through I)
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Hi,

You can use SUMPRODUCT, change the range to your table array and adjust cell reference ranges:

Book3.xlsx
ABCDEFGHIJKLM
1a135bcdefgh
2b2
3c3
4d4
5e5
6f6
7g7
8h8
9i9
10j10
Sheet782
Cell Formulas
RangeFormula
D1D1=SUMPRODUCT((A1:A10=G1:M1)*B1:B10)


Using your references, it'll probably look something like this, although I recommend Not to use Entire Column references:

Excel Formula:
=SUMPRODUCT((PivTbl_LookUp!A:A=G33:X33)*PivTbl_LookUp!B:B)
 
Last edited:
Upvote 0
Not sure if I did this correctly when compared to my formula that I provided, but
=SUMPRODUCT((PivTbl_LookUp!A:A=G22:X22)*PivTbl_LookUp!B:B)
Is giving me an outcome of 774 and it should be 477
I must have made a mistake
 
Upvote 0
In OP, you had G33, now you're using G22, could that be the problem?
 
Upvote 0
omg, I cannot believe I did that, yes, that fixed perfectly. and thank you so much for the assistance
 
Upvote 0
You're welcome, glad to help.

As I commented at the bottom of my Post #2, if possible, don't use entire column references, there's over 1 million rows in a column, try limiting to what you know would be more than adequate,
like A1:A50000 & B1:B50000 (for better performance and faster calculations)
 
Upvote 0

Forum statistics

Threads
1,213,585
Messages
6,114,518
Members
448,575
Latest member
hycrow

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
Back
Top