multiple lookup_value

I have a spreadsheet with a list of source codes in column A and the number
of downloads associated with each source code in column B.

What i'd like to do is add the number of downloads from a range of source
codes together. I've been using the VLOOKUP function but this is getting a
little long.

To put it another way, i want to find the number of downloads for each of 5
different source codes and add them together.

I've tried using SUMIF but can't seem to make it work correctly.

Any assistance would be very gratefully received.
Many thanks

=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A20,{"Code1","Code2","Code3","Code4","Code5
"},0))),B2:B20)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Inter" <[email protected]> wrote in message
news:[email protected]...
> I have a spreadsheet with a list of source codes in column A and the
number
> of downloads associated with each source code in column B.
>
> What i'd like to do is add the number of downloads from a range of source
> codes together. I've been using the VLOOKUP function but this is getting a
> little long.
>
> To put it another way, i want to find the number of downloads for each of
5
> different source codes and add them together.
>
> I've tried using SUMIF but can't seem to make it work correctly.
>
> Any assistance would be very gratefully received.
> Many thanks

try this SUMIF=(A:A,"=sourcecode",B:B) for each sourcecode



"Inter" wrote:

> I have a spreadsheet with a list of source codes in column A and the number
> of downloads associated with each source code in column B.
>
> What i'd like to do is add the number of downloads from a range of source
> codes together. I've been using the VLOOKUP function but this is getting a
> little long.
>
> To put it another way, i want to find the number of downloads for each of 5
> different source codes and add them together.
>
> I've tried using SUMIF but can't seem to make it work correctly.
>
> Any assistance would be very gratefully received.
> Many thanks

Try Sumproduct.


If you are having difficulties, post sufficient details and someone will help. It could be something like:
=SUMPRODUCT(--($A$1:$A$100=C8)*($B$1:$B$100))

Or even

=SUMPRODUCT(SUMIF(A2:A20,{"Code1","Code2","Code3","Code4","Code5"},B2:B20))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
>
=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A20,{"Code1","Code2","Code3","Code4","Code5
> "},0))),B2:B20)
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Inter" <[email protected]> wrote in message
> news:[email protected]...
> > I have a spreadsheet with a list of source codes in column A and the
> number
> > of downloads associated with each source code in column B.
> >
> > What i'd like to do is add the number of downloads from a range of
source
> > codes together. I've been using the VLOOKUP function but this is getting
a
> > little long.
> >
> > To put it another way, i want to find the number of downloads for each
of
> 5
> > different source codes and add them together.
> >
> > I've tried using SUMIF but can't seem to make it work correctly.
> >
> > Any assistance would be very gratefully received.
> > Many thanks
>
>

I'm not sure SUMPRODUCT is the right formul as i'm not trying to multiply
anything.

The data looks like this;

Source Downloads
10155 200
10156 300
10157 400
10158 500
10159 600

and i'm trying to write a formula that will give me the sum of the downloads
for 10155, 10156 and 10158 (1000)

I've tried both VLOOKUP and SUMIF with arrays
(=VLOOKUP({10156,10155,10158},A1:A5,2,FALSE) and
=SUMIF(A1:A5,{10156,10155,10158},B1:B5) respectively) but neither of these
give me the answer i'm looking for.

Cheers
Stuart

I'm not sure SUMPRODUCT is the right formul as i'm not trying to multiply
anything.

The data looks like this;

Source Downloads
10155 200
10156 300
10157 400
10158 500
10159 600

and i'm trying to write a formula that will give me the sum of the downloads
for 10155, 10156 and 10158 (1000)

I've tried both VLOOKUP and SUMIF with arrays
(=VLOOKUP({10156,10155,10158},A1:A5,2,FALSE) and
=SUMIF(A1:A5,{10156,10155,10158},B1:B5) respectively) but neither of these
give me the answer i'm looking for.

Cheers
Stuart

I should say that i have got the right answer with multiple VLOOKUP formulas
(i.e. vlookup(....)+vlookup(....) etc but i'm just trying to find out if
there's a way to do the same thing with a slightly shorter formula

Insert another field and with the required condition set values for a common
set of values and then run a pivot table on this field. see if this works.

"Inter" wrote:

> I'm not sure SUMPRODUCT is the right formul as i'm not trying to multiply
> anything.
>
> The data looks like this;
>
> Source Downloads
> 10155 200
> 10156 300
> 10157 400
> 10158 500
> 10159 600
>
> and i'm trying to write a formula that will give me the sum of the downloads
> for 10155, 10156 and 10158 (1000)
>
> I've tried both VLOOKUP and SUMIF with arrays
> (=VLOOKUP({10156,10155,10158},A1:A5,2,FALSE) and
> =SUMIF(A1:A5,{10156,10155,10158},B1:B5) respectively) but neither of these
> give me the answer i'm looking for.
>
> Cheers
> Stuart
>

Well you may think that, but you are wrong

=SUMPRODUCT(SUMIF(A2:A20,{"10155",10156,10158},B2:B20))


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Inter" <[email protected]> wrote in message
news:[email protected]...
> I'm not sure SUMPRODUCT is the right formul as i'm not trying to multiply
> anything.
>
> The data looks like this;
>
> Source Downloads
> 10155 200
> 10156 300
> 10157 400
> 10158 500
> 10159 600
>
> and i'm trying to write a formula that will give me the sum of the
downloads
> for 10155, 10156 and 10158 (1000)
>
> I've tried both VLOOKUP and SUMIF with arrays
> (=VLOOKUP({10156,10155,10158},A1:A5,2,FALSE) and
> =SUMIF(A1:A5,{10156,10155,10158},B1:B5) respectively) but neither of these
> give me the answer i'm looking for.
>
> Cheers
> Stuart
>

It works, but I meant

=SUMPRODUCT(SUMIF(A2:A20,{10155,10156,10158},B2:B20))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bob Phillips" <[email protected]> wrote in message
news:%[email protected]...
> Well you may think that, but you are wrong
>
> =SUMPRODUCT(SUMIF(A2:A20,{"10155",10156,10158},B2:B20))
>
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Inter" <[email protected]> wrote in message
> news:[email protected]...
> > I'm not sure SUMPRODUCT is the right formul as i'm not trying to
multiply
> > anything.
> >
> > The data looks like this;
> >
> > Source Downloads
> > 10155 200
> > 10156 300
> > 10157 400
> > 10158 500
> > 10159 600
> >
> > and i'm trying to write a formula that will give me the sum of the
> downloads
> > for 10155, 10156 and 10158 (1000)
> >
> > I've tried both VLOOKUP and SUMIF with arrays
> > (=VLOOKUP({10156,10155,10158},A1:A5,2,FALSE) and
> > =SUMIF(A1:A5,{10156,10155,10158},B1:B5) respectively) but neither of
these
> > give me the answer i'm looking for.
> >
> > Cheers
> > Stuart
> >
>
>

Maybe this is completely off target, but have you thought to use a pivot
table? It is highly versatile, great at collating date, and reliable. I
used it for a colleague (more intuition than ability on my part) and it saved
him the better part of a day and half each month!

You might have to fiddle with the settings (ie dont just accept the 'count
off ???" option for the data).

regards,

--
Peter


"Bob Phillips" wrote:

> It works, but I meant
>
> =SUMPRODUCT(SUMIF(A2:A20,{10155,10156,10158},B2:B20))
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Bob Phillips" <[email protected]> wrote in message
> news:%[email protected]...
> > Well you may think that, but you are wrong
> >
> > =SUMPRODUCT(SUMIF(A2:A20,{"10155",10156,10158},B2:B20))
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "Inter" <[email protected]> wrote in message
> > news:[email protected]...
> > > I'm not sure SUMPRODUCT is the right formul as i'm not trying to
> multiply
> > > anything.
> > >
> > > The data looks like this;
> > >
> > > Source Downloads
> > > 10155 200
> > > 10156 300
> > > 10157 400
> > > 10158 500
> > > 10159 600
> > >
> > > and i'm trying to write a formula that will give me the sum of the
> > downloads
> > > for 10155, 10156 and 10158 (1000)
> > >
> > > I've tried both VLOOKUP and SUMIF with arrays
> > > (=VLOOKUP({10156,10155,10158},A1:A5,2,FALSE) and
> > > =SUMIF(A1:A5,{10156,10155,10158},B1:B5) respectively) but neither of
> these
> > > give me the answer i'm looking for.
> > >
> > > Cheers
> > > Stuart
> > >
> >
> >
>
>
>

I was wrong. Mr Bob Phillips, you were right.

Thank you so much.

Stuart


"Bob Phillips" wrote:

> It works, but I meant
>
> =SUMPRODUCT(SUMIF(A2:A20,{10155,10156,10158},B2:B20))
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Bob Phillips" <[email protected]> wrote in message
> news:%[email protected]...
> > Well you may think that, but you are wrong
> >
> > =SUMPRODUCT(SUMIF(A2:A20,{"10155",10156,10158},B2:B20))
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "Inter" <[email protected]> wrote in message
> > news:[email protected]...
> > > I'm not sure SUMPRODUCT is the right formul as i'm not trying to
> multiply
> > > anything.
> > >
> > > The data looks like this;
> > >
> > > Source Downloads
> > > 10155 200
> > > 10156 300
> > > 10157 400
> > > 10158 500
> > > 10159 600
> > >
> > > and i'm trying to write a formula that will give me the sum of the
> > downloads
> > > for 10155, 10156 and 10158 (1000)
> > >
> > > I've tried both VLOOKUP and SUMIF with arrays
> > > (=VLOOKUP({10156,10155,10158},A1:A5,2,FALSE) and
> > > =SUMIF(A1:A5,{10156,10155,10158},B1:B5) respectively) but neither of
> these
> > > give me the answer i'm looking for.
> > >
> > > Cheers
> > > Stuart
> > >
> >
> >
>
>
>