Excel 2007 : Vlookup & SUM

[Jflann1]

I want to create a formula that will lookup an item in a table array and return the sum of multiple column index numbers. This is what I have so far:

=VLOOKUP(B45,'Qtr2 CY BEx'!$B$26:$CF$580,{6,12,18},FALSE)

It works, but only partially. It returns only what it finds in column 6. I thought the {} would accomplish what I needed.

I did try this and it seemed to work, =VLOOKUP(B45,'Qtr2 CY BEx'!$B$26:$CF$580,6,FALSE) + =VLOOKUP(B45,'Qtr2 CY BEx'!$B$26:$CF$580,12,FALSE) ....and so on.


but I really don't want to have to repeat this multiple times throughout such a large spreadsheet, I'm bound to screw it up.



Does anyone have any suggestions?

Thanks!

[NBVC]

Try:

=SUMPRODUCT(VLOOKUP(B45,'Qtr2 CY BEx'!$B$26:$CF$580,{6,12,18},FALSE))

[twiggywales]

how about sumifs?

something like this

sumifs.xlsx

or what NVBC said lol beat to it again

[Jflann1]

Try:

=SUMPRODUCT(VLOOKUP(B45,'Qtr2 CY BEx'!$B$26:$CF$580,{6,12,18},FALSE))

That worked wonderfully! I can't thank you enough. I'm on a deadline and I'm trying to revamp this monstrous spreadsheet that I didn't create! You are now my new best friend!!

Thanks NBVC!

[NBVC]

You're welcome and good luck!