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

multiple lookup_value
 

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Inter" wrote in message
...
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

multiple lookup_value
 

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

multiple lookup_value
 

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" wrote in message
...

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Inter" wrote in message
...
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

multiple lookup_value
 

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))


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=532238

multiple lookup_value
 

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

multiple lookup_value
 

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

multiple lookup_value
 

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

multiple lookup_value
 

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" wrote in message
...
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

multiple lookup_value
 

It works, but I meant

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bob Phillips" wrote in message
...
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" wrote in message
...
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

multiple lookup_value
 

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:B2 0))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bob Phillips" wrote in message
...
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" wrote in message
...
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

multiple lookup_value
 

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:B2 0))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bob Phillips" wrote in message
...
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" wrote in message
...
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