| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookups (sum of)
Hi
I hope someone can help. I think I've solved this before, but cannot remember for the life of me how to do it! I have some data that I want to produce some summary figures from: E.G Col A Col B 1000 10 1001 20 1002 30 1003 40 1004 50 1005 60 What I want to do is do a vlookup for say 1001 to 1003 inclusive, giving the result as the sum of the figures in Col B. In this example, the answer would be 90. Also, would it be possible to miss numbers out, e.g getting the results from 1000 and then 1003 to 1005? Hope that makes sense and thanks in advance for your help. Caroline |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookups (sum of)
Caroline,
There are a couple of ways, here's one =SUMPRODUCT((A1:A6=1001)*(A1:A6<=1003)*(B1:B6)) Mike "Caroline" wrote: Hi I hope someone can help. I think I've solved this before, but cannot remember for the life of me how to do it! I have some data that I want to produce some summary figures from: E.G Col A Col B 1000 10 1001 20 1002 30 1003 40 1004 50 1005 60 What I want to do is do a vlookup for say 1001 to 1003 inclusive, giving the result as the sum of the figures in Col B. In this example, the answer would be 90. Also, would it be possible to miss numbers out, e.g getting the results from 1000 and then 1003 to 1005? Hope that makes sense and thanks in advance for your help. Caroline |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookups (sum of)
Hi Mike
Thanks for your help, I think it's going to work, but here is the formula and i can't get it to work. I thought it might be because the data in column A was stored as text. I've changed it to numbers, but it still won't do it. I tried it on a simple list on the same sheet, and it worked fine! =SUMPRODUCT((Actuals!A2:A630=1830100)*(Actuals!A2 :A630=<1890300)*(C2:C630)) Thanks, sorry to be a pain! "Caroline" wrote: Hi I hope someone can help. I think I've solved this before, but cannot remember for the life of me how to do it! I have some data that I want to produce some summary figures from: E.G Col A Col B 1000 10 1001 20 1002 30 1003 40 1004 50 1005 60 What I want to do is do a vlookup for say 1001 to 1003 inclusive, giving the result as the sum of the figures in Col B. In this example, the answer would be 90. Also, would it be possible to miss numbers out, e.g getting the results from 1000 and then 1003 to 1005? Hope that makes sense and thanks in advance for your help. Caroline |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookups (sum of)
Caroline,
You were very close. try this =SUMPRODUCT((Actuals!A2:A630=1830100)*(Actuals!A2 :A630<=1890300)*(C2:C630)) The difference is I changed =< in the second condition to <= Mike "Caroline" wrote: Hi Mike Thanks for your help, I think it's going to work, but here is the formula and i can't get it to work. I thought it might be because the data in column A was stored as text. I've changed it to numbers, but it still won't do it. I tried it on a simple list on the same sheet, and it worked fine! =SUMPRODUCT((Actuals!A2:A630=1830100)*(Actuals!A2 :A630=<1890300)*(C2:C630)) Thanks, sorry to be a pain! "Caroline" wrote: Hi I hope someone can help. I think I've solved this before, but cannot remember for the life of me how to do it! I have some data that I want to produce some summary figures from: E.G Col A Col B 1000 10 1001 20 1002 30 1003 40 1004 50 1005 60 What I want to do is do a vlookup for say 1001 to 1003 inclusive, giving the result as the sum of the figures in Col B. In this example, the answer would be 90. Also, would it be possible to miss numbers out, e.g getting the results from 1000 and then 1003 to 1005? Hope that makes sense and thanks in advance for your help. Caroline |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookups (sum of)
Another way using SUMIF()
=SUMIF(A1:A6,"=1001",B1:B6)-SUMIF(A1:A6,"1003",B1:B6) If this post helps click Yes --------------- Jacob Skaria "Caroline" wrote: Hi I hope someone can help. I think I've solved this before, but cannot remember for the life of me how to do it! I have some data that I want to produce some summary figures from: E.G Col A Col B 1000 10 1001 20 1002 30 1003 40 1004 50 1005 60 What I want to do is do a vlookup for say 1001 to 1003 inclusive, giving the result as the sum of the figures in Col B. In this example, the answer would be 90. Also, would it be possible to miss numbers out, e.g getting the results from 1000 and then 1003 to 1005? Hope that makes sense and thanks in advance for your help. Caroline |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookups (sum of)
Mike
You are a star! It's now working perfectly, it's going to save me a huge amount of time. I presume if I just want to pick up 2 codes rather than a range of codes, i can change the = to just =? Thanks Caroline "Mike H" wrote: Caroline, You were very close. try this =SUMPRODUCT((Actuals!A2:A630=1830100)*(Actuals!A2 :A630<=1890300)*(C2:C630)) The difference is I changed =< in the second condition to <= Mike "Caroline" wrote: Hi Mike Thanks for your help, I think it's going to work, but here is the formula and i can't get it to work. I thought it might be because the data in column A was stored as text. I've changed it to numbers, but it still won't do it. I tried it on a simple list on the same sheet, and it worked fine! =SUMPRODUCT((Actuals!A2:A630=1830100)*(Actuals!A2 :A630=<1890300)*(C2:C630)) Thanks, sorry to be a pain! "Caroline" wrote: Hi I hope someone can help. I think I've solved this before, but cannot remember for the life of me how to do it! I have some data that I want to produce some summary figures from: E.G Col A Col B 1000 10 1001 20 1002 30 1003 40 1004 50 1005 60 What I want to do is do a vlookup for say 1001 to 1003 inclusive, giving the result as the sum of the figures in Col B. In this example, the answer would be 90. Also, would it be possible to miss numbers out, e.g getting the results from 1000 and then 1003 to 1005? Hope that makes sense and thanks in advance for your help. Caroline |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookups (sum of)
Glad too help
I presume if I just want to pick up 2 codes rather than a range of codes, i can change the = to just =? Correct Mike "Caroline" wrote: Mike You are a star! It's now working perfectly, it's going to save me a huge amount of time. I presume if I just want to pick up 2 codes rather than a range of codes, i can change the = to just =? Thanks Caroline "Mike H" wrote: Caroline, You were very close. try this =SUMPRODUCT((Actuals!A2:A630=1830100)*(Actuals!A2 :A630<=1890300)*(C2:C630)) The difference is I changed =< in the second condition to <= Mike "Caroline" wrote: Hi Mike Thanks for your help, I think it's going to work, but here is the formula and i can't get it to work. I thought it might be because the data in column A was stored as text. I've changed it to numbers, but it still won't do it. I tried it on a simple list on the same sheet, and it worked fine! =SUMPRODUCT((Actuals!A2:A630=1830100)*(Actuals!A2 :A630=<1890300)*(C2:C630)) Thanks, sorry to be a pain! "Caroline" wrote: Hi I hope someone can help. I think I've solved this before, but cannot remember for the life of me how to do it! I have some data that I want to produce some summary figures from: E.G Col A Col B 1000 10 1001 20 1002 30 1003 40 1004 50 1005 60 What I want to do is do a vlookup for say 1001 to 1003 inclusive, giving the result as the sum of the figures in Col B. In this example, the answer would be 90. Also, would it be possible to miss numbers out, e.g getting the results from 1000 and then 1003 to 1005? Hope that makes sense and thanks in advance for your help. Caroline |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookups (sum of)
Hi Mike
Sorry to bother you again. Can you see the problem with this formula? I don't need to pick up a range of codes, I need a selection of different codes, but from the same data as before. This is returning zero as the answer. (Sorry it's long, but thought it was easier to send the whole thing!) =+SUMPRODUCT((Actuals!A2:A630=5010111)*(Actuals!A2 :A630=5010211)*(Actuals!A2:A630=5120111)*(Actuals! A2:A630=5200111)*(Actuals!A2:A630=5200211)*(Actual s!A2:A630=5260111)*(Actuals!A2:A630=5260211)*(Actu als!A2:A630=5400111)*(Actuals!A2:A630=5400211)*(Ac tuals!A2:A630=5400311)*(Actuals!A2:A630=5401211)*( Actuals!A2:A630=5440111)*(Actuals!A2:A630=5480111) *(Actuals!A2:A630=5640111)*(Actuals!A2:A630=564021 1)*(Actuals!C2:C630)) Thanks again Caroline "Caroline" wrote: Hi I hope someone can help. I think I've solved this before, but cannot remember for the life of me how to do it! I have some data that I want to produce some summary figures from: E.G Col A Col B 1000 10 1001 20 1002 30 1003 40 1004 50 1005 60 What I want to do is do a vlookup for say 1001 to 1003 inclusive, giving the result as the sum of the figures in Col B. In this example, the answer would be 90. Also, would it be possible to miss numbers out, e.g getting the results from 1000 and then 1003 to 1005? Hope that makes sense and thanks in advance for your help. Caroline |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Dependent vlookups - nested vlookups (maybe) | Excel Worksheet Functions | |||
| VLOOKUPS | Excel Discussion (Misc queries) | |||
| Vlookups | Excel Worksheet Functions | |||
| VLOOKUPS | Excel Worksheet Functions | |||
| vlookups | Excel Discussion (Misc queries) | |||
Linear Mode
