|
Vlookup
How do you use vlookup when you have several items you want to look up and
return a total value. In this example I want to add all 3 items for a total of 15. Using vlookup it only returns the first R5 for a total of 5 R5 5 R5 5 R5 5 |
Vlookup
If the data shown is in Col A & B of sheet2 and you have R5 in A1 of sheet1...
Enter this in B1 of sheet1 =SUMPRODUCT(--(Sheet2!A1:A100=A1),(Sheet2!B1:B100)) ------------------------------------- Pl. click ''''Yes'''' if this was helpful... "Networking Issues Error 0X80070005" wrote: How do you use vlookup when you have several items you want to look up and return a total value. In this example I want to add all 3 items for a total of 15. Using vlookup it only returns the first R5 for a total of 5 R5 5 R5 5 R5 5 |
Vlookup
Would I still use vlookup? I want to total R5 which would be 11 on the R5 on
the other page. All the other infor 1st page 2nd page R5 5 R5 11 R4 2 R4 2 R3 3 R3 3 R5 4 R2 3 R2 3 R5 2 "Sheeloo" wrote: If the data shown is in Col A & B of sheet2 and you have R5 in A1 of sheet1... Enter this in B1 of sheet1 =SUMPRODUCT(--(Sheet2!A1:A100=A1),(Sheet2!B1:B100)) ------------------------------------- Pl. click ''''Yes'''' if this was helpful... "Networking Issues Error 0X80070005" wrote: How do you use vlookup when you have several items you want to look up and return a total value. In this example I want to add all 3 items for a total of 15. Using vlookup it only returns the first R5 for a total of 5 R5 5 R5 5 R5 5 |
Vlookup
No... This will sum all rows (Col B values) where Sheet2 COL A matches ...the value in Sheet1 A1... If you want to copy it down then use this =SUMPRODUCT(--(Sheet2!A$1:A$100=A1),(Sheet2!B$1:B$100)) so that lookup range remains same... of course you need to change 100 to the last row of your data on Sheet2... ------------------------------------- Pl. click ''''Yes'''' if this was helpful... "Networking Issues Error 0X80070005" wrote: Would I still use vlookup? I want to total R5 which would be 11 on the R5 on the other page. All the other infor 1st page 2nd page R5 5 R5 11 R4 2 R4 2 R3 3 R3 3 R5 4 R2 3 R2 3 R5 2 "Sheeloo" wrote: If the data shown is in Col A & B of sheet2 and you have R5 in A1 of sheet1... Enter this in B1 of sheet1 =SUMPRODUCT(--(Sheet2!A1:A100=A1),(Sheet2!B1:B100)) ------------------------------------- Pl. click ''''Yes'''' if this was helpful... "Networking Issues Error 0X80070005" wrote: How do you use vlookup when you have several items you want to look up and return a total value. In this example I want to add all 3 items for a total of 15. Using vlookup it only returns the first R5 for a total of 5 R5 5 R5 5 R5 5 |
Vlookup
Thank you
"Sheeloo" wrote: No... This will sum all rows (Col B values) where Sheet2 COL A matches ...the value in Sheet1 A1... If you want to copy it down then use this =SUMPRODUCT(--(Sheet2!A$1:A$100=A1),(Sheet2!B$1:B$100)) so that lookup range remains same... of course you need to change 100 to the last row of your data on Sheet2... ------------------------------------- Pl. click ''''Yes'''' if this was helpful... "Networking Issues Error 0X80070005" wrote: Would I still use vlookup? I want to total R5 which would be 11 on the R5 on the other page. All the other infor 1st page 2nd page R5 5 R5 11 R4 2 R4 2 R3 3 R3 3 R5 4 R2 3 R2 3 R5 2 "Sheeloo" wrote: If the data shown is in Col A & B of sheet2 and you have R5 in A1 of sheet1... Enter this in B1 of sheet1 =SUMPRODUCT(--(Sheet2!A1:A100=A1),(Sheet2!B1:B100)) ------------------------------------- Pl. click ''''Yes'''' if this was helpful... "Networking Issues Error 0X80070005" wrote: How do you use vlookup when you have several items you want to look up and return a total value. In this example I want to add all 3 items for a total of 15. Using vlookup it only returns the first R5 for a total of 5 R5 5 R5 5 R5 5 |
Vlookup
How do I use this on 2 different worksheets
"Networking Issues Error 0X80070005" wrote: Thank you "Sheeloo" wrote: No... This will sum all rows (Col B values) where Sheet2 COL A matches ...the value in Sheet1 A1... If you want to copy it down then use this =SUMPRODUCT(--(Sheet2!A$1:A$100=A1),(Sheet2!B$1:B$100)) so that lookup range remains same... of course you need to change 100 to the last row of your data on Sheet2... ------------------------------------- Pl. click ''''Yes'''' if this was helpful... "Networking Issues Error 0X80070005" wrote: Would I still use vlookup? I want to total R5 which would be 11 on the R5 on the other page. All the other infor 1st page 2nd page R5 5 R5 11 R4 2 R4 2 R3 3 R3 3 R5 4 R2 3 R2 3 R5 2 "Sheeloo" wrote: If the data shown is in Col A & B of sheet2 and you have R5 in A1 of sheet1... Enter this in B1 of sheet1 =SUMPRODUCT(--(Sheet2!A1:A100=A1),(Sheet2!B1:B100)) ------------------------------------- Pl. click ''''Yes'''' if this was helpful... "Networking Issues Error 0X80070005" wrote: How do you use vlookup when you have several items you want to look up and return a total value. In this example I want to add all 3 items for a total of 15. Using vlookup it only returns the first R5 for a total of 5 R5 5 R5 5 R5 5 |
Vlookup
Will this work on different workbooks?
"Sheeloo" wrote: If the data shown is in Col A & B of sheet2 and you have R5 in A1 of sheet1... Enter this in B1 of sheet1 =SUMPRODUCT(--(Sheet2!A1:A100=A1),(Sheet2!B1:B100)) ------------------------------------- Pl. click ''''Yes'''' if this was helpful... "Networking Issues Error 0X80070005" wrote: How do you use vlookup when you have several items you want to look up and return a total value. In this example I want to add all 3 items for a total of 15. Using vlookup it only returns the first R5 for a total of 5 R5 5 R5 5 R5 5 |
| All times are GMT +1. The time now is 02:21 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com