Summing vlookup cells
 

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?

Summing vlookup cells
 

On Mon, 16 Oct 2006 13:55:01 -0700, John K
wrote:

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
__________________________

Summing vlookup cells
 

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.


"Richard Buttrey" wrote:

On Mon, 16 Oct 2006 13:55:01 -0700, John K
wrote:

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
__________________________

Summing vlookup cells
 

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



John K wrote:
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.


"Richard Buttrey" wrote:

On Mon, 16 Oct 2006 13:55:01 -0700, John K
wrote:

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
__________________________