Re: Summing vlookups?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Peo Sjoblom (PeoSjoblom_at_discussions.microsoft.com)
Date: 12/17/04 

Date: Fri, 17 Dec 2004 11:23:01 -0800

You can still do it, assume the named range is MyTable, then use

=SUMPRODUCT(--(INDEX(MyTable,,1)=B1),INDEX(MyTable,,B2))

the first 1 in index tells it to find the match in the first column just
like in a vlookup, then B2 tells what column to sum where A is the lookup
value

Regards,

Peo Sjoblom

"Patti" wrote:

> Thanks for the quick reply Frank, but the number in cell B2 is the column for
> the Vlookup and it changes. The problem that I had with the simple SumIf is
> that the column to sum will change.
>
> "Frank Kabel" wrote:
>
> > Hi
> > try something like
> > =SUMIF('sheet1'!A1:A100,B2,'sheet1'!B1:B100)
> >
> > --
> > Regards
> > Frank Kabel
> > Frankfurt, Germany
> > "Patti" <Patti@discussions.microsoft.com> schrieb im Newsbeitrag
> > news:774121E7-9E21-462E-BFB7-4E6723667E8E@microsoft.com...
> > > I'm stuck! Here's what I'm trying to do:
> > >
> > > Vlookup($B$1,Named Range,$B$2,False)
> > >
> > > This works, but there's more than one match for $B$1 in column A. I want
> > > to
> > > sum the values in the column indicated in cell B2 if the data in column A
> > > match the data in cell B1 --
> > >
> > > Sumif(A:A,B1,[Column indicated in cell B2])
> > >
> > > The problem is that the column with the values to be summed will change,
> > > so
> > > a simple Sumif won't work. Thanks!
> >
> >
> >

Relevant Pages

  • Summing values in a specific column in a named range
    ... Can someone tell me how do I sum the values in a specific column in a ... named range. ... probably were from Alan Beban but I could be wrong here. ... Best regards and thanks in advance. ...
    (microsoft.public.excel.worksheet.functions)
  • Dynamic range in chart
    ... I have created a named range called Net_CLI which has the following ... Presently I expect the vlookup part of the above formula to return H, ... What is the mistake am doing above. ...
    (microsoft.public.excel.charting)
  • Re: COMPLEX "IF" FORMULAS
    ... at the help section of vlookup ... The vlookup will allow you to look for the sum of the column ... > Firstly, as I'm not very experienced at excel, I'm waaaay over my head ... > the amount. ...
    (microsoft.public.excel.programming)
  • Re: Sum the results of a vlookup
    ... Wow - thanks Ashish! ... value - this is why the sum is not working. ... of all the cells that show up in column F as a result of the vlookup ... performed in each of those cells. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Lookup Not working as expected
    ... >> As far as I know, LOOKUP only works correctly if the lookup range is ... >> Regards, ... I tried using VLOOKUP and HLOOKUP but I seem to get ... > contents of the cell beside the cell that contains the lowest value, ...
    (microsoft.public.excel.newusers)