Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 __________________________ |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 __________________________ |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 __________________________ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking Groups of cells between workbooks | Excel Discussion (Misc queries) | |||
Vlookup and Cells with Formulas | Excel Discussion (Misc queries) | |||
summing cells with text and numbers | Excel Worksheet Functions | |||
vlookup cannot get data for some cells | Excel Worksheet Functions | |||
VLOOKUP - Multiple cells in lookup value | Excel Worksheet Functions |