Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions
|
|||
|
|||
adding up vlookup values in one column and in between them
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. |
#2
Posted to microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions
|
|||
|
|||
adding up vlookup values in one column and in between them
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. "observer" wrote: 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. |
#3
Posted to microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions
|
|||
|
|||
adding up vlookup values in one column and in between them
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. "observer" wrote: 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. |
#4
Posted to microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions
|
|||
|
|||
adding up vlookup values in one column and in between them
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. On Sun, 3 Feb 2008 07:31:00 -0800, JLatham <HelpFrom @ Jlathamsite.com.(removethis) wrote: 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. "observer" wrote: 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. |
#5
Posted to microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions
|
|||
|
|||
adding up vlookup values in one column and in between them
I think my follow up posting will help you with this.
"observer" wrote: 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. On Sun, 3 Feb 2008 07:31:00 -0800, JLatham <HelpFrom @ Jlathamsite.com.(removethis) wrote: 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. "observer" wrote: 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. |
#6
Posted to microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions
|
|||
|
|||
adding up vlookup values in one column and in between them
On Sun, 3 Feb 2008 08:36:00 -0800, JLatham <HelpFrom @
Jlathamsite.com.(removethis) wrote: I think my follow up posting will help you with this. 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 !! |
#7
Posted to microsoft.public.excel.misc, microsoft.public.excel.worksheet.functions
|
|||
|
|||
adding up vlookup values in one column and in between them
Here are three more methods.
One requires no formulas. http://www.freefilehosting.net/download/3bd8i |
#8
Posted to microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions
|
|||
|
|||
adding up vlookup values in one column and in between them
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. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... 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. "observer" wrote: 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. |
#9
Posted to microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions
|
|||
|
|||
adding up vlookup values in one column and in between them
the numbers are decreasing in value as you
go down the column 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) -- Biff Microsoft Excel MVP <observer wrote in message ... 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. |
#10
Posted to microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions
|
|||
|
|||
adding up vlookup values in one column and in between them
Does help, and often simplifies things, when you get more little details like
that, doesn't it. Thanks for jumping in with that one. "T. Valko" wrote: the numbers are decreasing in value as you go down the column 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) -- Biff Microsoft Excel MVP <observer wrote in message ... 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. |
#11
Posted to microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions
|
|||
|
|||
adding up vlookup values in one column and in between them
Yeah, the devil is in the details!
-- Biff Microsoft Excel MVP "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... Does help, and often simplifies things, when you get more little details like that, doesn't it. Thanks for jumping in with that one. "T. Valko" wrote: the numbers are decreasing in value as you go down the column 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) -- Biff Microsoft Excel MVP <observer wrote in message ... 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. |
#12
Posted to microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions
|
|||
|
|||
adding up vlookup values in one column and in between them
On Sun, 3 Feb 2008 14:31:46 -0500, "T. Valko"
wrote: the numbers are decreasing in value as you go down the column 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) 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! |
#13
Posted to microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions
|
|||
|
|||
adding up vlookup values in one column and in between them
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. "observer" wrote: On Sun, 3 Feb 2008 14:31:46 -0500, "T. Valko" wrote: the numbers are decreasing in value as you go down the column 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) 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! |
#14
Posted to microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions
|
|||
|
|||
adding up vlookup values in one column and in between them
Jerry summed it up nicely.
You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... 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. "observer" wrote: On Sun, 3 Feb 2008 14:31:46 -0500, "T. Valko" wrote: the numbers are decreasing in value as you go down the column 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) 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
adding up vlookup values in one column and in between them | Excel Discussion (Misc queries) | |||
Adding multiple values in one column based on multiple values of the same value (text) in another column | Excel Discussion (Misc queries) | |||
Adding into another column (VLOOKUP) | Excel Discussion (Misc queries) | |||
Adding numerical values based on multiple values in another column | Excel Worksheet Functions | |||
Adding Values Based on a Separate Column | Excel Discussion (Misc queries) |