Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
External Links and Multiple Instances | Excel Discussion (Misc queries) | |||
AHHHH-Get Data from Multiple Excel workbooks | Excel Discussion (Misc queries) | |||
How do i auto create multiple files from 1 with multiple sheets | Excel Worksheet Functions | |||
Adding multiple worksheets | Excel Worksheet Functions | |||
Can I get the mode, min, and max with multiple criteria? | Excel Discussion (Misc queries) |