Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Why can't I sum several vlookups ??
Currently, I can use vlookup to find the value in a cell for any w'sheet.
Why can't I sum a series of vlookups? Each vlookup would scan a range of sheets, then find the correct row (if it exists, if not skip this sheet), and these values could be summed. =SUM(FIRST:LAST!B3) Works, but does not allow any variation among rows. To use this, I have to match the rows of a hundred sheets!! =VLOOKUP(A6,FIRST:LAST!A4:D100,5,FALSE) Does not give an error, but does not give a value either. =VLOOKUP(A6,'*',A4:D100,5,FALSE) Same result. Could I use something like =SUM(VLOOKUP(............) ? I tried asking for advice and got some ideas that work IF I had a PH.D. in Excel. Isn't there a simple way to sum a value, in a range of sheets, where the value appears in different rows in each sheet, but the same column? IF COLUMN A CONTAINS A UNIQUE VALUE (ie: DATE or PART#) TO SEARCH. IF THE COLUMNS IN EACH SHEET ARE THE SAME. Right now I'm guessing this can only be done by very smart people who make their living teaching Excel or writing books about Excel. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...et.f unctions |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Why can't I sum several vlookups ??
Try the below which will lookup the value in C1 in Sheet1,Sheet2 ColA and add
the corresponding values in ColB =SUMPRODUCT(SUMIF(INDIRECT("'"& {"sheet1","sheet2"} &"'!A:A"),C1, INDIRECT("'"& {"sheet1","sheet2"} &"'!B:B"))) 'To try with more sheets Sheet1, Sheet2,Sheet3....Sheet25 =SUMPRODUCT(SUMIF(INDIRECT("'"&"sheet"&ROW(INDIREC T("1:25"))& "'!A:A"),C1,INDIRECT("'"&"sheet"&ROW(INDIRECT("1:2 5"))&"'!B:B"))) If this post helps click Yes --------------- Jacob Skaria "Friday" wrote: Currently, I can use vlookup to find the value in a cell for any w'sheet. Why can't I sum a series of vlookups? Each vlookup would scan a range of sheets, then find the correct row (if it exists, if not skip this sheet), and these values could be summed. =SUM(FIRST:LAST!B3) Works, but does not allow any variation among rows. To use this, I have to match the rows of a hundred sheets!! =VLOOKUP(A6,FIRST:LAST!A4:D100,5,FALSE) Does not give an error, but does not give a value either. =VLOOKUP(A6,'*',A4:D100,5,FALSE) Same result. Could I use something like =SUM(VLOOKUP(............) ? I tried asking for advice and got some ideas that work IF I had a PH.D. in Excel. Isn't there a simple way to sum a value, in a range of sheets, where the value appears in different rows in each sheet, but the same column? IF COLUMN A CONTAINS A UNIQUE VALUE (ie: DATE or PART#) TO SEARCH. IF THE COLUMNS IN EACH SHEET ARE THE SAME. Right now I'm guessing this can only be done by very smart people who make their living teaching Excel or writing books about Excel. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...et.f unctions |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Why can't I sum several vlookups ??
There is no simple way of doing this since Excel is quite limited using
multiple sheets. There are some quite complicated methods of getting this done in one fell swoop, however you seem to have been given some advice in this regard already. Btw, why not use a summary sheet where you pull the vlookup to from multiple sheets and then sum the summary sheet. Other methods require very complicated formulas like this =VLOOKUP(A2,INDIRECT("'"&INDEX($H$2:$H$10,MATCH(TR UE,COUNTIF(INDIRECT("'"&$H$2:$H$10&"'!B2:B100"),A2 )0,0))&"'!B2:F100"),3,0) array entered where you put the sheet names in H2:H10, where A2 is the lookup value and B2:F100 the lookup table -- Regards, Peo Sjoblom "Friday" wrote in message ... Currently, I can use vlookup to find the value in a cell for any w'sheet. Why can't I sum a series of vlookups? Each vlookup would scan a range of sheets, then find the correct row (if it exists, if not skip this sheet), and these values could be summed. =SUM(FIRST:LAST!B3) Works, but does not allow any variation among rows. To use this, I have to match the rows of a hundred sheets!! =VLOOKUP(A6,FIRST:LAST!A4:D100,5,FALSE) Does not give an error, but does not give a value either. =VLOOKUP(A6,'*',A4:D100,5,FALSE) Same result. Could I use something like =SUM(VLOOKUP(............) ? I tried asking for advice and got some ideas that work IF I had a PH.D. in Excel. Isn't there a simple way to sum a value, in a range of sheets, where the value appears in different rows in each sheet, but the same column? IF COLUMN A CONTAINS A UNIQUE VALUE (ie: DATE or PART#) TO SEARCH. IF THE COLUMNS IN EACH SHEET ARE THE SAME. Right now I'm guessing this can only be done by very smart people who make their living teaching Excel or writing books about Excel. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...et.f unctions |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Why can't I sum several vlookups ??
JACOB,
Thank you for your suggestion. However, your solution is exactly what I was referring to in my post when I previously received suggestions which required a PH D in Excel. I don't know what half that stuff means. I can't rename my sheets if I understand you. Isn't there a simple & direct way to sum a series of vlookups? That's why I am making this a suggestion for Microsoft this time. Steve "Jacob Skaria" wrote: Try the below which will lookup the value in C1 in Sheet1,Sheet2 ColA and add the corresponding values in ColB =SUMPRODUCT(SUMIF(INDIRECT("'"& {"sheet1","sheet2"} &"'!A:A"),C1, INDIRECT("'"& {"sheet1","sheet2"} &"'!B:B"))) 'To try with more sheets Sheet1, Sheet2,Sheet3....Sheet25 =SUMPRODUCT(SUMIF(INDIRECT("'"&"sheet"&ROW(INDIREC T("1:25"))& "'!A:A"),C1,INDIRECT("'"&"sheet"&ROW(INDIRECT("1:2 5"))&"'!B:B"))) If this post helps click Yes --------------- Jacob Skaria "Friday" wrote: Currently, I can use vlookup to find the value in a cell for any w'sheet. Why can't I sum a series of vlookups? Each vlookup would scan a range of sheets, then find the correct row (if it exists, if not skip this sheet), and these values could be summed. =SUM(FIRST:LAST!B3) Works, but does not allow any variation among rows. To use this, I have to match the rows of a hundred sheets!! =VLOOKUP(A6,FIRST:LAST!A4:D100,5,FALSE) Does not give an error, but does not give a value either. =VLOOKUP(A6,'*',A4:D100,5,FALSE) Same result. Could I use something like =SUM(VLOOKUP(............) ? I tried asking for advice and got some ideas that work IF I had a PH.D. in Excel. Isn't there a simple way to sum a value, in a range of sheets, where the value appears in different rows in each sheet, but the same column? IF COLUMN A CONTAINS A UNIQUE VALUE (ie: DATE or PART#) TO SEARCH. IF THE COLUMNS IN EACH SHEET ARE THE SAME. Right now I'm guessing this can only be done by very smart people who make their living teaching Excel or writing books about Excel. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...et.f unctions |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Why can't I sum several vlookups ??
Well.. you can rename your sheets..Specify the sheetnames in the range J1:J5
(no blanks please) and try the below formula =SUMPRODUCT(SUMIF(INDIRECT("'"& J1:J5 &"'!A:A"),C1, INDIRECT("'"& J1:J5 &"'!B:B"))) If this post helps click Yes --------------- Jacob Skaria "Friday" wrote: JACOB, Thank you for your suggestion. However, your solution is exactly what I was referring to in my post when I previously received suggestions which required a PH D in Excel. I don't know what half that stuff means. I can't rename my sheets if I understand you. Isn't there a simple & direct way to sum a series of vlookups? That's why I am making this a suggestion for Microsoft this time. Steve "Jacob Skaria" wrote: Try the below which will lookup the value in C1 in Sheet1,Sheet2 ColA and add the corresponding values in ColB =SUMPRODUCT(SUMIF(INDIRECT("'"& {"sheet1","sheet2"} &"'!A:A"),C1, INDIRECT("'"& {"sheet1","sheet2"} &"'!B:B"))) 'To try with more sheets Sheet1, Sheet2,Sheet3....Sheet25 =SUMPRODUCT(SUMIF(INDIRECT("'"&"sheet"&ROW(INDIREC T("1:25"))& "'!A:A"),C1,INDIRECT("'"&"sheet"&ROW(INDIRECT("1:2 5"))&"'!B:B"))) If this post helps click Yes --------------- Jacob Skaria "Friday" wrote: Currently, I can use vlookup to find the value in a cell for any w'sheet. Why can't I sum a series of vlookups? Each vlookup would scan a range of sheets, then find the correct row (if it exists, if not skip this sheet), and these values could be summed. =SUM(FIRST:LAST!B3) Works, but does not allow any variation among rows. To use this, I have to match the rows of a hundred sheets!! =VLOOKUP(A6,FIRST:LAST!A4:D100,5,FALSE) Does not give an error, but does not give a value either. =VLOOKUP(A6,'*',A4:D100,5,FALSE) Same result. Could I use something like =SUM(VLOOKUP(............) ? I tried asking for advice and got some ideas that work IF I had a PH.D. in Excel. Isn't there a simple way to sum a value, in a range of sheets, where the value appears in different rows in each sheet, but the same column? IF COLUMN A CONTAINS A UNIQUE VALUE (ie: DATE or PART#) TO SEARCH. IF THE COLUMNS IN EACH SHEET ARE THE SAME. Right now I'm guessing this can only be done by very smart people who make their living teaching Excel or writing books about Excel. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...et.f unctions |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Why can't I sum several vlookups ??
I don't know what half that stuff means.
Here's your chance to teach someone! -- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... Well.. you can rename your sheets..Specify the sheetnames in the range J1:J5 (no blanks please) and try the below formula =SUMPRODUCT(SUMIF(INDIRECT("'"& J1:J5 &"'!A:A"),C1, INDIRECT("'"& J1:J5 &"'!B:B"))) If this post helps click Yes --------------- Jacob Skaria "Friday" wrote: JACOB, Thank you for your suggestion. However, your solution is exactly what I was referring to in my post when I previously received suggestions which required a PH D in Excel. I don't know what half that stuff means. I can't rename my sheets if I understand you. Isn't there a simple & direct way to sum a series of vlookups? That's why I am making this a suggestion for Microsoft this time. Steve "Jacob Skaria" wrote: Try the below which will lookup the value in C1 in Sheet1,Sheet2 ColA and add the corresponding values in ColB =SUMPRODUCT(SUMIF(INDIRECT("'"& {"sheet1","sheet2"} &"'!A:A"),C1, INDIRECT("'"& {"sheet1","sheet2"} &"'!B:B"))) 'To try with more sheets Sheet1, Sheet2,Sheet3....Sheet25 =SUMPRODUCT(SUMIF(INDIRECT("'"&"sheet"&ROW(INDIREC T("1:25"))& "'!A:A"),C1,INDIRECT("'"&"sheet"&ROW(INDIRECT("1:2 5"))&"'!B:B"))) If this post helps click Yes --------------- Jacob Skaria "Friday" wrote: Currently, I can use vlookup to find the value in a cell for any w'sheet. Why can't I sum a series of vlookups? Each vlookup would scan a range of sheets, then find the correct row (if it exists, if not skip this sheet), and these values could be summed. =SUM(FIRST:LAST!B3) Works, but does not allow any variation among rows. To use this, I have to match the rows of a hundred sheets!! =VLOOKUP(A6,FIRST:LAST!A4:D100,5,FALSE) Does not give an error, but does not give a value either. =VLOOKUP(A6,'*',A4:D100,5,FALSE) Same result. Could I use something like =SUM(VLOOKUP(............) ? I tried asking for advice and got some ideas that work IF I had a PH.D. in Excel. Isn't there a simple way to sum a value, in a range of sheets, where the value appears in different rows in each sheet, but the same column? IF COLUMN A CONTAINS A UNIQUE VALUE (ie: DATE or PART#) TO SEARCH. IF THE COLUMNS IN EACH SHEET ARE THE SAME. Right now I'm guessing this can only be done by very smart people who make their living teaching Excel or writing books about Excel. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...et.f unctions |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookups | Excel Worksheet Functions | |||
2 VLookups | Excel Worksheet Functions | |||
Dependent vlookups - nested vlookups (maybe) | Excel Worksheet Functions | |||
VLOOKUPS | Excel Worksheet Functions | |||
Vlookups | Excel Discussion (Misc queries) |