Need Excel help please!

[KeithTalent]

Hey there!

I'm doing some Excel work (in 2007) and I am running into an issue when summing certain VLookup results.

Not sure how to explain this, but anyway what I am doing is looking up multiple values in several columns on the one sheet and summing the values from another sheet. So I look up A1, B1, C1 from Sheet1 and sum the returned numbers from Sheet2, for example (there are 700+ rows and some cells have no information in them, hence the IF formula added in there):

VLOOKUP(A384,'87 AC F10'!$A$6:$D$1108,4,FALSE)+IF(B384="",0,VLOOKUP(B384,'87 AC F10'!$A$6:$D$1108,4,FALSE))+IF(C384="",0,VLOOKUP(C384,'87 AC F10'!$A$6:$D$1108,4,FALSE))+IF(D384="",0,VLOOKUP(D384,'87 AC F10'!$A$6:$D$1108,4,FALSE))

etc, etc.

This works fine as long as there is a result on Sheet2 to be found, but if there is no result I get the dreaded #N/A 🙁 result and I have no idea how to get around that. I tried messing with ISERROR, but it did not seem to work if there were multiple results I was trying to sum.

Does anyone have thoughts on how I can fix this? Let me know if I need to provide more information, or if anything is unclear.

Thanks!

KT

 

[dawza]

Correct me if this is wrong, but from what I can infer, this is what you are trying to do:

On sheet 1, you have a list of references:

Axx
Bxx
Cxx
Dxx...and so forth

On your other sheets, you have data corresponding to your reference values; you have several sheets, each with different data, but with the same reference values. You want to sum data across several sheets.

Couldn't you just do the vlookup for each reference and then sum at the end? If you have a cell in your data table that has no value, vlookup should return zero. Or, instead of using IF(B384="", 0...), does IF(B384<>"", vlookup..., 0) work?

 

[Binky]

You should look into sumproduct functions.

Example: lookup cells are A1 - A3, data description is in E1-E5, and data is in F1-F5

A1-A5 are:
aaa
bbb
ccc
ddd
eee

E1-F5 are:
aaa 123
aaa 222
bbb 111
ccc 2341
ccc 2342

This formula would result in 345, since all of the "aaa" values sum up to 345
=SUMPRODUCT(($E$1:$E$5=A1)*($F$1:$F$5))

This formula would result in 4683, since all of the "ccc" values sum up to 4683
=SUMPRODUCT(($E$1:$E$5=A3)*($F$1:$F$5))

Missing data shouldnt affect these formulas - they will just return a zero.

 

[KeithTalent]

Correct me if this is wrong, but from what I can infer, this is what you are trying to do:

On sheet 1, you have a list of references:

Axx
Bxx
Cxx
Dxx...and so forth

On your other sheets, you have data corresponding to your reference values; you have several sheets, each with different data, but with the same reference values. You want to sum data across several sheets.

Couldn't you just do the vlookup for each reference and then sum at the end? If you have a cell in your data table that has no value, vlookup should return zero. Or, instead of using IF(B384="", 0...), does IF(B384<>"", vlookup..., 0) work?

Sort of. I only have two sheets. Sheet1 has values in columns A-L (some cells in the range do not have any value) that I am looking up on Sheet2 and summing the corresponding results in one cell on Sheet1. The data on Sheet2 is essentially just two columns, column A being any number of instances of what I am looking up from A-L from Sheet1 and a value in Column B that I am trying to sum, depending on the lookup.

Hopefully that makes things a bit more clear.

I think what you referenced could work, but it would be very cumbersome considering the amount of data involved. I'll fiddle around with it a bit to see. Thanks.

KT

 

[KeithTalent]

You should look into sumproduct functions.

Example: lookup cells are A1 - A3, data description is in E1-E5, and data is in F1-F5

A1-A5 are:
aaa
bbb
ccc
ddd
eee

E1-F5 are:
aaa 123
aaa 222
bbb 111
ccc 2341
ccc 2342

This formula would result in 345, since all of the "aaa" values sum up to 345
=SUMPRODUCT(($E$1:$E$5=A1)*($F$1:$F$5))
 
This formula would result in 4683, since all of the "ccc" values sum up to 4683
=SUMPRODUCT(($E$1:$E$5=A3)*($F$1:$F$5))

Missing data shouldnt affect these formulas - they will just return a zero.

Holy shit man, I think this worked and it's way easier than what I was trying. You rock, thanks so much!

Thanks to you too dawza for the assistance!

😎

KT