Vlookup

  • Thread starter Thread starter Networking Issues Error 0X80070005
  • Start date Start date
N

Networking Issues Error 0X80070005

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
 
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))
 
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
 
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...
 
Thank you

Sheeloo said:
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...
Click to expand...
 
Will this work on different workbooks?

Sheeloo said:
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))
Click to expand...
 
Back
Top