Vlookup & addition

[vanair]

Happy Christmas and New Year,

I'm hoping someone more intelligent than myself can get out of a bind. I have multiple ranges in a spread sheet. I am trying to write a formula that will go out to each range in succession and look for a part number, upon finding return a quantity and them move on to the next range duplicating the above process. The formula should tally the grand total of all numbers found. I have it working except that not all of my items are in all ranges. If the item that I am searching for is in all ranges my formula works but if there is one or more of the ranges that doesn't have that particular value it returns an #n/a instead of totalling those that do have it. If I use a true instead of false in my [range_lookup] I get an incorrect answer. My formula for a given cell is listed below. This is with the true argument which does not work.

=SUM(VLOOKUP($A135,$P$104:$S$170,4,TRUE)+VLOOKUP($A135,$T$104:$W$170,4,TRUE)+VLOOKUP($A135,$X$104:$AA$170,4,TRUE)+VLOOKUP($A135,$AB$104:$AE$170,4,TRUE)+VLOOKUP($A135,$AF$104:$AI202,4,TRUE)+VLOOKUP($A135,$AJ$104:$AM$170,4,TRUE)+VLOOKUP($A135,$AN$104:$AQ$170,4,TRUE)+VLOOKUP($A135,$AR$104:$AU$170,4,TRUE)+VLOOKUP($A135,$AV$104:$AY$170,4,TRUE)+VLOOKUP($A135,$AZ$104:$BC$170,4,TRUE)+VLOOKUP($A135,$BD$104:$BG$170,4,TRUE)+VLOOKUP($A135,$BH$104:$BK$170,4,TRUE)+VLOOKUP($A135,$BL$104:$BO$170,4,TRUE)+VLOOKUP($A135,$BP$104:$BS$170,4,TRUE)+VLOOKUP($A135,$BT$104:$BW$170,4,TRUE)+VLOOKUP($A135,$BX$104:$CA$170,4,TRUE)+VLOOKUP($A135,$CB$104:$CE$170,4,TRUE)+VLOOKUP($A135,$CF$104:$CI$170,4,TRUE))

[teylyn]

Hi Vanair,

welcome to the forum.

This problem might be easier to solve if you post a small sample of your data. A chain of Vlookups might not be the best option. I could spend some time trying to re-create a data sample from your formula above, but it will be easier if you just posted your data ....

cheers

[vanair]

Thanks,

I have attached the spreadsheet. You will find the data I'm working with in the purple color cells. There are a bank of ranges to the right. I am working in column L. The ranges are a bill of materials for different given ways to manufacture our equipment. I want to be able to do a mock build of equipment and find the amount of parts needed to achieve this build. You will find the ranges each have their own calculations. Thanks for the help.

[deadlyduck]

Not entirely sure but possibly a sumproduct formula along the following lines might do it:

=sumproduct(--(P104:cf104=a135)*(S170:ci170)

EDIT:

Sorry- I see you have uploaded sample data- will have a look at that now.

[teylyn]

Instead of stringing together Vlookups, you could string together SUMIF, like this

=SUMIF($P$103:$P$144,A103,$S$103:$S$144)+SUMIF($T$103:$T$149,A103,$W$104:$W$149)+ ... etc

Seeing that your ranges have a varying number of rows and the value in column A may or may not be present in the range, this will at least not produce errors. Althought it's still ugly ...

In 2007 you could use SUMIFS with multiple arguments

[deadlyduck]

I;d go with Teylyn's approach-I came up with the following:

=sumproduct(--(P104:p170=a135)*(S104:s170))+sumproduct(--(t104:t170=a135)*(w104:w170))+ {etc- repeat this pattern for your entire range)

[teylyn]

I just changed mine to SUMIF. No real need for SUMPRODUCT if there's only one condition....

[vanair]

Thanks a lot for your help. I will give this a try. You have saved my forehead from significant damage due to the desk banging it was getting.

Vince

[deadlyduck]

Thanks for the tip teylyn- will try remember to apply that next time!

Useful to know that the simpler SUMIF works too- I tried out this and got the right answer:

=SUMIF($P$104:$CF$170,A134,$S$104:$CI$170)

[teylyn]

Deadlyduck,

with SUMPRODUCT you'd normally do double unary or multiplication, but not both, so

=sumproduct(--(P104:p170=a135)*(S104:s170))

should either be

=sumproduct((P104:p170=a135)*(S104:s170))

or

=sumproduct(--(P104:p170=a135),(S104:s170))