Find a Range of Data

[John Sutton]

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

 

[Guest]

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

 

[John Sutton]

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.

 

[Biff]

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]

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