sum VLOOKUPs (not cells)

Originally Posted by zbor

=sum(vlookup1, vlookup2...)

Yes, but suppose I have 100 VLOOKUPs, I don't want to manually write the 100 VLOOKUPs. I want to only specify a range and on that range it will preform VLOOKUP and then sum the results.

Originally Posted by martindwilson

what exactly do you mean? 100's of vlookups?
if you vlookup on a range you only get one result any way
do you mean for every cell in range that contains ,say ,"cash" sum its corresponding value?

No. I mean, I have 100 different elements. Each of those elements I want to lookup in the table and return a value. Then I want to sum all of the 100 returned values. (All this without the returned values appearing in cells)

Originally Posted by DonkeyOte

And so the next questions would be ... where are you 100 values stored and what is the range that contains the values of interest (ie those to be summed) ?

Well, my question is actually can I calculate the sum without storing the 100 values returned by VLOOKUP. I would like to do something like this:

Please Login or Register  to view this content.

But use a sort of sum function instead of writing such a long line...

The reason, sgdled, that you're getting these questions-instead-of-answers, is because your data seems to be arranged in quite a peculiar way (from what I can tell) and the natural response of the contributors here is to try to "make things better" - it's more like consultancy than robotics, if that makes sense. There's a good chance you will save a lot of time be organising the spreadsheet differently a few steps further back up the chain.

CC

Originally Posted by DonkeyOte

No, my point was more you have 100 values to look for, where are they ?

Based off your prior formula example they are seemingly stored in A1:A100, appearing in column 1 of named range with values in column 2 of named range, in which case the below should work:

Please Login or Register  to view this content.

This is very close to what I want. However, there is still a problem if I have multiple entries in A1:A100. This formula, will count only one of them.

Originally Posted by DonkeyOte

Not sure I understand ... you're saying if A1:A100 contain the same value twice the result should be duplicated ? ie

Please Login or Register  to view this content.

Thanks DonkeyOte!
One more thing. Let's say I now want to calculate the following sum:

Please Login or Register  to view this content.

In other words, sumproduct between the values found in DataTable for A1:A100 and B1:B100.
Is that possible?

Originally Posted by DonkeyOte

Probably easier to revert to a traditional Array, eg:

Please Login or Register  to view this content.

Wow, thank you DonkeyOte!!
You are a true genius