adding up vlookup values in one column and in between them

O

observer

Let me restate an earlier post so maybe its easier to understand...

I want to use vlookup for 2 values in a column and then I want to be
able to add them and all the numbers in between them (in that column)
to another cell. Can this be done and how? Please no VBA as I'm
not familiar with it. Thanks.
 
J

JLatham

I think you're going to be better off using a variation of the SUMPRODUCT()
function. You don't tell us what type of values you're looking up - dates,
text, numbers? Nor have you mentioned their relationship to each other. It
would help to know that.

But lets say you have the information in 2 columns, and that the 'key'
values are in ascending order like this:
1 .1
1234 .2
2345 .3
3456 .4
4567 .5
9000 .6

and you want the sum of all numbers associated with values 1234 through
3456, inclusive, then this would do it for you:
=SUMPRODUCT(--(A1:A6>=1234),--(A1:A6<=3456),B1:B6)
If you put the start/end values in a couple of cells, you can have a more
flexible situation. Lets say you put the lower limit in D1 and the upper
limit in E1, then your formula becomes:
=SUMPRODUCT(--(A1:A6>=D1),--(A1:A6<=E1),B1:B6)

The results for both formulas should be .9 (.2+.3+.4)

But for this all to work as a "between" operation, the key values in column
A are going to have to be in sequential order, either ascending or descending.

Hope this helps some.
 
J

JLatham

Here's a more direct answer to your question. First I have to explain a
couple of things: the formula assumes that your data is in column A (column
#1) ; you've put your lower limit in cell D1 and the upper limit in E1; and
finally, as with a VLOOKUP(), the entries in that column are unique.

=SUM(INDIRECT(ADDRESS(MATCH(D1,A:A),1) & ":" & ADDRESS(MATCH(E1,A:A),1)))

Now, you wanted to add that value to the value in another cell. If that
other cell has a formula in it already, you can simply add this to that
formula:
+ SUM(INDIRECT(ADDRESS(MATCH(D1,A:A),1) & ":" & ADDRESS(MATCH(E1,A:A),1)))

That is, if your existing formual is something like:
=AVG(B9:B99)
then after adding the above formula to it, it would look like:
=AVG(B9:B99) + SUM(INDIRECT(ADDRESS(MATCH(D1,A:A),1) & ":" &
ADDRESS(MATCH(E1,A:A),1)))

But if your cell has a user entered value in it, like 66, then what you're
going to have to do to get the user's entered value of 66 added in with the
result of our SUM() formula is to have the 66 placed somewhere else (lets say
it gets put into F1), then in the final results cell you could have the
formula:
=F1 + SUM(INDIRECT(ADDRESS(MATCH(D1,A:A),1) & ":" & ADDRESS(MATCH(E1,A:A),1)))

Again, I hope this helps some.
 
O

observer

Appreciate the reply.
In my case the cells all contain numbers but the problem is that the
vlookups may be any random 2 cells in one column and the numbers are
decreasing in value as you go down the column (amortization schedule).

thank you again.
 
O

observer

I think my follow up posting will help you with this.
Click to expand...


Appreciate that. I'll have to study it more closely as I'm not
familiar with many excel commands but learning as I go / need.

Thank you again !!
 
R

Ragdyer

For the sake of archive accuracy, this is *not* true:

<<<"But for this all to work as a "between" operation, the key values in
column
A are going to have to be in sequential order, either ascending or
descending>>>"

In the scenario you painted, Sumproduct will return correct results, no
matter what order Column A is in.
 
T

T. Valko

the numbers are decreasing in value as you
go down the column
Click to expand...

Sum values in column A that are between 2 boundaries (inclusive):

99
87
66
59
50
49
28

C1 = 87
D1 = 49

=SUMIF(A1:A7,"<="&C1)-SUMIF(A1:A7,"<"&D1)
 
J

JLatham

Does help, and often simplifies things, when you get more little details like
that, doesn't it. Thanks for jumping in with that one.
 
O

observer

Sum values in column A that are between 2 boundaries (inclusive):

99
87
66
59
50
49
28

C1 = 87
D1 = 49

=SUMIF(A1:A7,"<="&C1)-SUMIF(A1:A7,"<"&D1)
Click to expand...


Thank you. I think this simplicity will help me a lot. I understand
tho that others may not have been able to help me without more details
and I apologize for that but in my defense when you are totally
ignorant, it's hard to know which details are necessary to give :( .

This newsgroup is still a nice bunch of people 'cause they don't tell
you how stupid you are for asking a question (like myself in this
case). Always appreciate this newsgroup for their wealth of
knowledge, willingness to offer it for free and in a short time.

Thank you to all of you for the help!
 
J

JLatham

Not a real problem, we realize that often people don't know what's important
or pertinent and what's not. There's also often a 'vocabulary' problem,
which we deal with as needed (example: you don't have lines around a cell,
you have borders).

And sometimes we even find ourselves at the other end of the pendulum swing:
so much information that it's hard to pick out what's important and what's
not.

As far as anyone claiming a question is a dumb one - not in these forums.
The whole concept is to assist users of all experience/skill levels in Excel,
and we often get the first-time user. The idea here is to share our
experience and knowledge, not tell others how much 'smarter' we are than they
are. It's not a question of 'smarts'; it's a matter of experience with the
tool.

I'm simply glad that a good solution was found for you.
 
Top