Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find a Range of Data
XL 2002
I have a seriously complicated spreadsheet, consisting of multiple worksheets that I am constantly updating. I would like to replace a Lookup formula with something that will return a range of data values. Currently, I have a formula that looks like this =-Lookup("4020 AL",TrialBl!$A$1:$B$2109)-Lookup("4020 AR",TrialBl!$A$1:$B$2109)-Lookup("4020 AZ",TrialBl!$A$1:$B$2109)-Lookup("4020 CO",TrialBl!$A$1:$B$2109) The real formula is really much larger than this. What I'd like to do is be able to look in the data and return the sum of the return vector who have a "4020" in the Lookup Vector Any help greatly appreciated John KBS |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find a Range of Data
Since you are a little vague on exactly what is the desired result here is a
reference for you to look at. I beleive Example 9 at the bottom is close to what you are trying to do but it is kinda hard to tell from your description. http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- HTH... Jim Thomlinson "John Sutton" wrote: XL 2002 I have a seriously complicated spreadsheet, consisting of multiple worksheets that I am constantly updating. I would like to replace a Lookup formula with something that will return a range of data values. Currently, I have a formula that looks like this =-Lookup("4020 AL",TrialBl!$A$1:$B$2109)-Lookup("4020 AR",TrialBl!$A$1:$B$2109)-Lookup("4020 AZ",TrialBl!$A$1:$B$2109)-Lookup("4020 CO",TrialBl!$A$1:$B$2109) The real formula is really much larger than this. What I'd like to do is be able to look in the data and return the sum of the return vector who have a "4020" in the Lookup Vector Any help greatly appreciated John KBS |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find a Range of Data
James,
My apologies, and many thanks for taking the time to respond. I will look more at this page you sent, although #9 doesn't appear to be the solution I am looking for. What I have is an array of account numbers (with sub accts appended to make them unique), and the corresponding account balances. I want to total all accounts in the array that have an account number of "2020" 1100 4567.90 1120 KS 1234.56 2020 AL 345.67 2020 AZ 56734.56 There are only two columns of data, and I want to sum the amounts in column two if the value "2020" is contained in column one. I hope this explains it better. On Fri, 8 Sep 2006 12:11:02 -0700, Jim Thomlinson wrote: Since you are a little vague on exactly what is the desired result here is a reference for you to look at. I beleive Example 9 at the bottom is close to what you are trying to do but it is kinda hard to tell from your description. http://www.xldynamic.com/source/xld.SUMPRODUCT.html |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find a Range of Data
Try this:
=SUMPRODUCT(--(ISNUMBER(FIND(2020,A1:A4))),B1:B4) Better to use a cell to hold the criteria: F1 = 2020 =SUMPRODUCT(--(ISNUMBER(FIND(F1,A1:A4))),B1:B4) Biff "John Sutton" wrote in message ... James, My apologies, and many thanks for taking the time to respond. I will look more at this page you sent, although #9 doesn't appear to be the solution I am looking for. What I have is an array of account numbers (with sub accts appended to make them unique), and the corresponding account balances. I want to total all accounts in the array that have an account number of "2020" 1100 4567.90 1120 KS 1234.56 2020 AL 345.67 2020 AZ 56734.56 There are only two columns of data, and I want to sum the amounts in column two if the value "2020" is contained in column one. I hope this explains it better. On Fri, 8 Sep 2006 12:11:02 -0700, Jim Thomlinson wrote: Since you are a little vague on exactly what is the desired result here is a reference for you to look at. I beleive Example 9 at the bottom is close to what you are trying to do but it is kinda hard to tell from your description. http://www.xldynamic.com/source/xld.SUMPRODUCT.html |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find a Range of Data
Biff,
Thanks for your response. That didn't seem to do the trick either. I needed to get on with it, so I did the ugly version. As the data resides on a tab page by itself, and doesn't get manipulated by hand, I put the following in all cells in a column equaling the data size. =IF(ISNUMBER(SEARCH(F$1,$A3, 1))$B2,0) Which says if the first cell of this row contains the contents of cell F1, then give me the contents of the second cell of this row. Then I sum the column and get my answer. Crude, but it does what I need. Thanks for the help, hopefully I can contribute someday! John KBS On Fri, 8 Sep 2006 17:02:12 -0400, "Biff" wrote: Try this: =SUMPRODUCT(--(ISNUMBER(FIND(2020,A1:A4))),B1:B4) Better to use a cell to hold the criteria: F1 = 2020 =SUMPRODUCT(--(ISNUMBER(FIND(F1,A1:A4))),B1:B4) Biff "John Sutton" wrote in message .. . James, My apologies, and many thanks for taking the time to respond. I will look more at this page you sent, although #9 doesn't appear to be the solution I am looking for. What I have is an array of account numbers (with sub accts appended to make them unique), and the corresponding account balances. I want to total all accounts in the array that have an account number of "2020" 1100 4567.90 1120 KS 1234.56 2020 AL 345.67 2020 AZ 56734.56 There are only two columns of data, and I want to sum the amounts in column two if the value "2020" is contained in column one. I hope this explains it better. On Fri, 8 Sep 2006 12:11:02 -0700, Jim Thomlinson wrote: Since you are a little vague on exactly what is the desired result here is a reference for you to look at. I beleive Example 9 at the bottom is close to what you are trying to do but it is kinda hard to tell from your description. http://www.xldynamic.com/source/xld.SUMPRODUCT.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting a new line when external data changes | Excel Discussion (Misc queries) | |||
How can you sum the last 5 columns of data from a range of data | New Users to Excel | |||
Excel Macro to Copy & Paste | Excel Worksheet Functions | |||
named range, data validation: list non-selected items, and new added items | Excel Discussion (Misc queries) | |||
Sort pages? | Excel Discussion (Misc queries) |