Summing vlookup cells


G

Guest

I have a sheet with 5 colums that has a finance number and 3 cols that have
numbers. I gave it a range res. On my main page I have it do a vlookup of
the finance number =vlookup(b2,res,3,false). Now the problem is that we have
offices that have multiple zip codes with the same finance number. What I
want to do is when the vlookup looks up the finance number, it adds all the
numbers that go with that finance number. For example if a2 and a5 have the
same finance number in the res range then it would add c2 and c5 in the cell
I put the vlookup in. Can this be done?
 
Ad

Advertisements

R

Richard Buttrey

I have a sheet with 5 colums that has a finance number and 3 cols that have
numbers. I gave it a range res. On my main page I have it do a vlookup of
the finance number =vlookup(b2,res,3,false). Now the problem is that we have
offices that have multiple zip codes with the same finance number. What I
want to do is when the vlookup looks up the finance number, it adds all the
numbers that go with that finance number. For example if a2 and a5 have the
same finance number in the res range then it would add c2 and c5 in the cell
I put the vlookup in. Can this be done?


Sounds like you need a SumProduct formula not a Vlookup.

Try something like

=Sumproduct((a1:a100="financenumber")*(c1:c100))

Change the ranges to suit. Instead of hard coding the "financenumber",
you'd be better putting a cell reference.

HTH
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
 
G

Guest

Here is what I have =SUMPRODUCT((Results!C$2:C$551=B5)*(Results!E$2:E$551)),
but it doesn't work. I get a #N/A error. So it's back to the drawing board.
 
Ad

Advertisements

R

Richard

Are there any cells in C2:C551, or E2:E551 which evaluate to an error?
That's the normal cause of an #N/A at this stage. Your formula looks
OK.

HTH
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top