multiple lookup_value

I

Inter

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
 
B

Bob Phillips

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
B

Bob Phillips

Or even

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
J

John James

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

Inter

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

Inter

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
 
M

Manoj

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.
 
B

Bob Phillips

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

Bob Phillips

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

Peter

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,
 
Top