Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How to get SUM of range of cells based on two separate dropdown selections.
I have 2 dropdowns (A2 and B2) on sheet 1 that both reference cells B2 through B75 on sheet 2. I want to be able to do a SUM of all cells in the B column of sheet 2 between what I select in dropdowns A2 and B2.
I can provide examples if this is not clear enough. TIA |
#2
|
|||
|
|||
Quote:
Dear Bretai2k, Good Afternoon. Take a look at it and tell me if it worked for you. =SUM(INDIRECT(CONCATENATE("Plan2!$B",MATCH(A2,Plan 2!$B$2:$B$75,0)+1,":$B",MATCH(B2,Plan2!$B$2:$B$75, 0)+1))) Fell free to ask anything about your question. Have a nice day.
__________________
I hope it can help you. Best regards, Marcilio Lobão --------------------------- Belo Horizonte, Brazil |
#3
|
|||
|
|||
Quote:
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to get SUM of range of cells based on two separate dropdown selections.
On Tue, 8 May 2012 15:30:30 +0000, Bretai2k wrote:
I have 2 dropdowns (A2 and B2) on sheet 1 that both reference cells B2 through B75 on sheet 2. I want to be able to do a SUM of all cells in the B column of sheet 2 between what I select in dropdowns A2 and B2. I can provide examples if this is not clear enough. TIA Try: =SUM(INDIRECT(A2&":"&B2)) (Assumes when you write "between" you want to also include the cells identified in A2 and B2). |
#5
|
|||
|
|||
Quote:
I´m sorry! As my Excel is in Portuguese the sheet here is called Plan. I forgot to change this for you. Try this one now. =SUM(INDIRECT(CONCATENATE("Sheet2!$B",MATCH(A2,She et2!$B$2:$B$75,0)+1,":$B",MATCH(B2,Sheet2!$B$2:$B$ 75, 0)+1))) Have a nice day.
__________________
I hope it can help you. Best regards, Marcilio Lobão --------------------------- Belo Horizonte, Brazil |
#6
|
|||
|
|||
Quote:
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to get SUM of range of cells based on two separate dropdown selections.
On Tue, 08 May 2012 21:25:19 -0400, Ron Rosenfeld wrote:
On Tue, 8 May 2012 15:30:30 +0000, Bretai2k wrote: I have 2 dropdowns (A2 and B2) on sheet 1 that both reference cells B2 through B75 on sheet 2. I want to be able to do a SUM of all cells in the B column of sheet 2 between what I select in dropdowns A2 and B2. I can provide examples if this is not clear enough. TIA Try: =SUM(INDIRECT(A2&":"&B2)) (Assumes when you write "between" you want to also include the cells identified in A2 and B2). I missed that the data and dropdowns are on separate sheets. If your formula is on Sheet 1, then use this: =SUM(INDIRECT("Sheet2!"&A2&":"&B2)) If your formula is not on Sheet1, try this: =SUM(INDIRECT("Sheet2!"&Sheet1!A2&":"&Sheet1!B2)) |
#8
|
|||
|
|||
Quote:
Dear Bretai2k, Good Morning. It´s very important always, the user attach here a worksheet with an example about your question. It´s quicker, easier and all the answers are effective on the solution. At first time you said: "...Originally Posted by Bretai2k I have 2 dropdowns (A2 and B2) on sheet 1 that both reference cells B2 through B75 on sheet 2. I want to be able to do a SUM of all cells in the B column of sheet 2 between what I select in dropdowns A2 and B2. I can provide examples if this is not clear enough.TIA ..." Then, the formula was built pointing to these cells. =SUM(INDIRECT(CONCATENATE("Sheet2!$B",MATCH(A2,She et2!$B$2:$B$75,0)+1,":$B",MATCH(B2,Sheet2!$B$2:$B$ 75, 0)+1)) NOW, you´re saying this: "...The actual range of cells I want to validate against is on Sheet1, cells B2 through B75, while the function is checking the start and end of the range on sheet 2, cells A2 and B2...." Then you need to invert the formula. Put on any cell on SHEET2 this fórmula: =SUM(INDIRECT(CONCATENATE("Sheet1!$B",MATCH(A2,She et1!$B$2:$B$75,0)+1,":$B",MATCH(B2,Sheet1!$B$2:$B$ 75, 0)+1)) Is this working for you now?
__________________
I hope it can help you. Best regards, Marcilio Lobão --------------------------- Belo Horizonte, Brazil |
#9
|
|||
|
|||
Quote:
Hope this helps you help me :D. Again, thank you for all of your help on this. |
#10
|
|||
|
|||
Quote:
Have a look at the attached, specifically the yellow cell (G6). Is this what you mean?? |
#11
|
|||
|
|||
Quote:
Thank you all again for your help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filter range based on results of multiple selections in a listbox | Excel Programming | |||
multiple dropdown selections | Excel Worksheet Functions | |||
Help with dropdown boxes and preset selections | Excel Programming | |||
How can I allow users to make multiple selections from a dropdown. | Excel Worksheet Functions | |||
Dropdown Selections in Excel - creating and choosing | Excel Worksheet Functions |