#value error when using a sum function that includes vlookup cell references

[Rusty315]

Hi all

I'm using this formula:

=SUM(VLOOKUP((B82&C82),wkone,4,0),VLOOKUP((B82&C82),wktwo,4,0),VLOOKUP((B82&C82),wkthree,4,0),VLOOKUP((B82&C82),wkfour,4,0),VLOOKUP((B82&C82),wkfive,4,0))

I get a #VALUE error where any value returned by one of the VLOOKUPs is text. I've done a little research and apparently the SUM function is meant to help avoid those errors as opposed to using a simple arithmetic arguments (=a2+b2) but I can't find anything about the use of functions within the sum itself.

I need the formula to ignore text entries. How can I go about doing that?

Appreciate any help!

Cheers

 

[theozz]

Like this

try this...

=SUMIF(INDEX(aaa,,1),101,INDEX(aaa,,3))+SUMIF(INDEX(bbb,,1),101,INDEX(bbb,,3))

may...save from error