Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Lookup value in cell and sum in two diff worksheets?
Say I have three worksheets, two of them contain data which holds names. I
need to sum up a certain persons name as they appear in the two different worksheets in the third worksheet. How do I go about this? I've tried using a SUMPRODUCT with a VLOOKUP, but I can't seem to get it right, PLEASE HELP! |
#2
|
|||
|
|||
Lookup value in cell and sum in two diff worksheets?
Just do a simple VLOOKUP(... in first sheet ...)+VLOOKUP(... in second sheet
....) -- HTH RP (remove nothere from the email address if mailing direct) "AZExcelNewbie" wrote in message ... Say I have three worksheets, two of them contain data which holds names. I need to sum up a certain persons name as they appear in the two different worksheets in the third worksheet. How do I go about this? I've tried using a SUMPRODUCT with a VLOOKUP, but I can't seem to get it right, PLEASE HELP! |
#3
|
|||
|
|||
Lookup value in cell and sum in two diff worksheets?
I get a nice error message... #N/A
=VLOOKUP(A1,Sheet1!A:A,1)+VLOOKUP(A1,Sheet2!A:A,1) "Bob Phillips" wrote: Just do a simple VLOOKUP(... in first sheet ...)+VLOOKUP(... in second sheet ....) -- HTH RP (remove nothere from the email address if mailing direct) "AZExcelNewbie" wrote in message ... Say I have three worksheets, two of them contain data which holds names. I need to sum up a certain persons name as they appear in the two different worksheets in the third worksheet. How do I go about this? I've tried using a SUMPRODUCT with a VLOOKUP, but I can't seem to get it right, PLEASE HELP! |
#4
|
|||
|
|||
Lookup value in cell and sum in two diff worksheets?
That means it is not finding the value in A1 in either sheet1 or sheet2.
What would you like to do in that circumstance, know about it and go fix that problem, or assume 0? -- HTH RP (remove nothere from the email address if mailing direct) "AZExcelNewbie" wrote in message ... I get a nice error message... #N/A =VLOOKUP(A1,Sheet1!A:A,1)+VLOOKUP(A1,Sheet2!A:A,1) "Bob Phillips" wrote: Just do a simple VLOOKUP(... in first sheet ...)+VLOOKUP(... in second sheet ....) -- HTH RP (remove nothere from the email address if mailing direct) "AZExcelNewbie" wrote in message ... Say I have three worksheets, two of them contain data which holds names. I need to sum up a certain persons name as they appear in the two different worksheets in the third worksheet. How do I go about this? I've tried using a SUMPRODUCT with a VLOOKUP, but I can't seem to get it right, PLEASE HELP! |
#5
|
|||
|
|||
Lookup value in cell and sum in two diff worksheets?
And in this case, since you're matching on text values, I'd assume that you
wanted an exact match. In general, your =vlookup() formula would look more like: =vlookup(a1,sheet1!a:b,2,false) (at least 2 columns (A:B) and bring back the stuff in column B) so you could ignore the errors with: =sum(if(iserror(vlookup(a1,sheet1!a:b,2,false)),0, vlookup(a1,sheet1!a:b,2,false)), if(iserror(vlookup(a1,sheet2!a:b,2,false)),0,vlook up(a1,sheet2!a:b,2,false))) (all one cell) Debra Dalgleish's has some notes you may like: http://www.contextures.com/xlFunctions02.html (for =vlookup()) and http://www.contextures.com/xlFunctions03.html (for =index(match())) AZExcelNewbie wrote: I get a nice error message... #N/A =VLOOKUP(A1,Sheet1!A:A,1)+VLOOKUP(A1,Sheet2!A:A,1) "Bob Phillips" wrote: Just do a simple VLOOKUP(... in first sheet ...)+VLOOKUP(... in second sheet ....) -- HTH RP (remove nothere from the email address if mailing direct) "AZExcelNewbie" wrote in message ... Say I have three worksheets, two of them contain data which holds names. I need to sum up a certain persons name as they appear in the two different worksheets in the third worksheet. How do I go about this? I've tried using a SUMPRODUCT with a VLOOKUP, but I can't seem to get it right, PLEASE HELP! -- Dave Peterson |
#6
|
|||
|
|||
Lookup value in cell and sum in two diff worksheets?
=sum(if(iserror(vlookup(a1,sheet1!a:b,2,false)),0,
vlookup(a1,sheet1!a:b,2,false)), if(iserror(vlookup(a1,sheet2!a:b,2,false)),0, vlookup(a1,sheet2!a:b,2,false))) (I got hit by line wrap in my post. This may look a little more clear. Dave Peterson wrote: And in this case, since you're matching on text values, I'd assume that you wanted an exact match. In general, your =vlookup() formula would look more like: =vlookup(a1,sheet1!a:b,2,false) (at least 2 columns (A:B) and bring back the stuff in column B) so you could ignore the errors with: =sum(if(iserror(vlookup(a1,sheet1!a:b,2,false)),0, vlookup(a1,sheet1!a:b,2,false)), if(iserror(vlookup(a1,sheet2!a:b,2,false)),0,vlook up(a1,sheet2!a:b,2,false))) (all one cell) Debra Dalgleish's has some notes you may like: http://www.contextures.com/xlFunctions02.html (for =vlookup()) and http://www.contextures.com/xlFunctions03.html (for =index(match())) AZExcelNewbie wrote: I get a nice error message... #N/A =VLOOKUP(A1,Sheet1!A:A,1)+VLOOKUP(A1,Sheet2!A:A,1) "Bob Phillips" wrote: Just do a simple VLOOKUP(... in first sheet ...)+VLOOKUP(... in second sheet ....) -- HTH RP (remove nothere from the email address if mailing direct) "AZExcelNewbie" wrote in message ... Say I have three worksheets, two of them contain data which holds names. I need to sum up a certain persons name as they appear in the two different worksheets in the third worksheet. How do I go about this? I've tried using a SUMPRODUCT with a VLOOKUP, but I can't seem to get it right, PLEASE HELP! -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|