If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Compare, Match & Sum HELP
I am looking for a formula that will compare 3 or 4 columns in 2 different
excel workbooks and if a match is found, sum a different column for the corresponding matched rows. Approximately 1500 rows would be checked. I was able to do it using a combination of; "sum", "if" and "and" in my formula, but the formula has to be repeated for each row and will only allow the formula to be long enough to do 4 rows before the formula gets to be too long for the cell. -- gen_chang |
#2
|
|||
|
|||
Compare, Match & Sum HELP
will only allow the formula to be long enough ..
Just a thought .. you could try simplifying the task by having everything done in one book rather than across books (eg: copy/move the relevant sheets from one book into the other, then formulate it there), and by using very short, simple sheetnames for the sheets involved (rename as eg: a,b,c ... or 1,2,3). Both steps would immediately cut down formulas' length drastically. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Fredw" wrote: I am looking for a formula that will compare 3 or 4 columns in 2 different excel workbooks and if a match is found, sum a different column for the corresponding matched rows. Approximately 1500 rows would be checked. I was able to do it using a combination of; "sum", "if" and "and" in my formula, but the formula has to be repeated for each row and will only allow the formula to be long enough to do 4 rows before the formula gets to be too long for the cell. -- gen_chang |
#3
|
|||
|
|||
Compare, Match & Sum HELP
While shortening the sheetnames would indeed decrease the invidual parts of
the formula, but at 1,500 lines, the formula will still to be too long for an single cell. I am thinking "vlookup", "sumif" or something that would encompass a "range" is the direction that I need to head. Thank-you for the reply, -- gen_chang "Max" wrote: will only allow the formula to be long enough .. Just a thought .. you could try simplifying the task by having everything done in one book rather than across books (eg: copy/move the relevant sheets from one book into the other, then formulate it there), and by using very short, simple sheetnames for the sheets involved (rename as eg: a,b,c ... or 1,2,3). Both steps would immediately cut down formulas' length drastically. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Fredw" wrote: I am looking for a formula that will compare 3 or 4 columns in 2 different excel workbooks and if a match is found, sum a different column for the corresponding matched rows. Approximately 1500 rows would be checked. I was able to do it using a combination of; "sum", "if" and "and" in my formula, but the formula has to be repeated for each row and will only allow the formula to be long enough to do 4 rows before the formula gets to be too long for the cell. -- gen_chang |
#4
|
|||
|
|||
Compare, Match & Sum HELP
Try posting your top cell formula ..
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Fredw" wrote in message ... While shortening the sheetnames would indeed decrease the invidual parts of the formula, but at 1,500 lines, the formula will still to be too long for an single cell. I am thinking "vlookup", "sumif" or something that would encompass a "range" is the direction that I need to head. Thank-you for the reply, -- gen_chang |
Thread Tools | |
Display Modes | |
|
|