| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup
I am trying to use vlookup to find all the item that equal a yes or no in a
table, and I want it to sum. For example table 1 yes 1 no 1 yes I can only get voolkup to pull a "1" value", how do I get it to sum all the yes values? Is there another function that I should use? -- Demetrio |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup
=Sumproduct((b1:b10)="yes")*(a1:a10))
will sum values in A when B=Yes HTH "Demetrio Valdez" wrote: I am trying to use vlookup to find all the item that equal a yes or no in a table, and I want it to sum. For example table 1 yes 1 no 1 yes I can only get voolkup to pull a "1" value", how do I get it to sum all the yes values? Is there another function that I should use? -- Demetrio |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup
Have a look in the help index for SUMIF
-- Don Guillett SalesAid Software "Demetrio Valdez" wrote in message ... I am trying to use vlookup to find all the item that equal a yes or no in a table, and I want it to sum. For example table 1 yes 1 no 1 yes I can only get voolkup to pull a "1" value", how do I get it to sum all the yes values? Is there another function that I should use? -- Demetrio |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup
I recreated your fomula with a simple spreadsheet and it doesn't work. I'm
not sure why. it seems that this formula will ad all items in A1 thru A10 if the condition is met at least once in B1 thru b10. I need to sum only this items on the same line in which the condition is met. Thanks I hope you can help :) -- Demetrio "Toppers" wrote: =Sumproduct((b1:b10)="yes")*(a1:a10)) will sum values in A when B=Yes HTH "Demetrio Valdez" wrote: I am trying to use vlookup to find all the item that equal a yes or no in a table, and I want it to sum. For example table 1 yes 1 no 1 yes I can only get voolkup to pull a "1" value", how do I get it to sum all the yes values? Is there another function that I should use? -- Demetrio |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup
Thanks but this I've tried several simple worksheet and I can only get it to
sum an entire range if the condition is met. I need it to be selective to the items that meet the condition. Thanks for you help :) -- Demetrio "Don Guillett" wrote: Have a look in the help index for SUMIF -- Don Guillett SalesAid Software "Demetrio Valdez" wrote in message ... I am trying to use vlookup to find all the item that equal a yes or no in a table, and I want it to sum. For example table 1 yes 1 no 1 yes I can only get voolkup to pull a "1" value", how do I get it to sum all the yes values? Is there another function that I should use? -- Demetrio |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup
Following on from Don's suggestion, try this:
=SUMIF(B1:B10,"Yes",A1:A10) Assumes your Yes/No answers are in B1 to B10 and the values to be added are in A1 to A10 - adjust the ranges to suit. Hope this helps. Pete On Mar 5, 7:51 pm, Demetrio Valdez wrote: Thanks but this I've tried several simple worksheet and I can only get it to sum an entire range if the condition is met. I need it to be selective to the items that meet the condition. Thanks for you help :) -- Demetrio "Don Guillett" wrote: Have a look in the help index for SUMIF -- Don Guillett SalesAid Software "Demetrio Valdez" wrote in ... I am trying to use vlookup to find all the item that equal a yes or no in a table, and I want it to sum. For example table 1 yes 1 no 1 yes I can only get voolkup to pull a "1" value", how do I get it to sum all the yes values? Is there another function that I should use? -- Demetrio- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup
By the way, you might need to use a semicolon instead of the commas.
Pete On Mar 5, 8:05 pm, "Pete_UK" wrote: Following on from Don's suggestion, try this: =SUMIF(B1:B10,"Yes",A1:A10) Assumes your Yes/No answers are in B1 to B10 and the values to be added are in A1 to A10 - adjust the ranges to suit. Hope this helps. Pete On Mar 5, 7:51 pm, Demetrio Valdez wrote: Thanks but this I've tried several simple worksheet and I can only get it to sum an entire range if the condition is met. I need it to be selective to the items that meet the condition. Thanks for you help :) -- Demetrio "Don Guillett" wrote: Have a look in the help index for SUMIF -- Don Guillett SalesAid Software "Demetrio Valdez" wrote in ... I am trying to use vlookup to find all the item that equal a yes or no in a table, and I want it to sum. For example table 1 yes 1 no 1 yes I can only get voolkup to pull a "1" value", how do I get it to sum all the yes values? Is there another function that I should use? -- Demetrio- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup
Don,
I must have donne soething wrong the first time, this work perfectly. Thank you very Much!! Pete and Topper, Thanks for yuor help! -- Demetrio "Don Guillett" wrote: Did you look at the help for SUMIF? Based on your example, the answer is 2 =SUMIF(E2:E5,"Yes",D2:D4) -- Don Guillett SalesAid Software "Demetrio Valdez" wrote in message ... Thanks but this I've tried several simple worksheet and I can only get it to sum an entire range if the condition is met. I need it to be selective to the items that meet the condition. Thanks for you help :) -- Demetrio "Don Guillett" wrote: Have a look in the help index for SUMIF -- Don Guillett SalesAid Software "Demetrio Valdez" wrote in message ... I am trying to use vlookup to find all the item that equal a yes or no in a table, and I want it to sum. For example table 1 yes 1 no 1 yes I can only get voolkup to pull a "1" value", how do I get it to sum all the yes values? Is there another function that I should use? -- Demetrio |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup
Glad to help
-- Don Guillett SalesAid Software "Demetrio Valdez" wrote in message ... Don, I must have donne soething wrong the first time, this work perfectly. Thank you very Much!! Pete and Topper, Thanks for yuor help! -- Demetrio "Don Guillett" wrote: Did you look at the help for SUMIF? Based on your example, the answer is 2 =SUMIF(E2:E5,"Yes",D2:D4) -- Don Guillett SalesAid Software "Demetrio Valdez" wrote in message ... Thanks but this I've tried several simple worksheet and I can only get it to sum an entire range if the condition is met. I need it to be selective to the items that meet the condition. Thanks for you help :) -- Demetrio "Don Guillett" wrote: Have a look in the help index for SUMIF -- Don Guillett SalesAid Software "Demetrio Valdez" wrote in message ... I am trying to use vlookup to find all the item that equal a yes or no in a table, and I want it to sum. For example table 1 yes 1 no 1 yes I can only get voolkup to pull a "1" value", how do I get it to sum all the yes values? Is there another function that I should use? -- Demetrio |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
| Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
| IF(a1="x",(vlookup 18K rows,2,false),(vlookup 18K,3,false)) RAM? | Excel Worksheet Functions | |||
| IF(AND(val1=VLOOKUP( );val2>=VLOOKUP( );val2<=VLOOKUP( );VLOOKUP( | Excel Worksheet Functions | |||
| Vlookup info being used without vlookup table attached? | Excel Worksheet Functions | |||
Linear Mode
