How to get SUM of range of cells based on two separate dropdown selections.

[Bretai2k]

I have 2 dropdowns (A2 and B2) on sheet 1 that both reference cells B
through B75 on sheet 2. I want to be able to do a SUM of all cells i
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

TI

 

[Mazzaropi]

I have 2 dropdowns (A2 and B2) on sheet 1 that both reference cells B
through B75 on sheet 2. I want to be able to do a SUM of all cells i
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

<<<<< *HELP from BRAZIL* >>>>>

Dear *Bretai2k*, Good Afternoon.

Take a look at it and tell me if it worked for you.

=SUM(INDIRECT(CONCATENATE(\"PLAN2!$B\",MATCH(A2,PLAN2!$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

 

[Ron Rosenfeld]

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

 

[Mazzaropi]

Concatenate might be what I am looking for, but I'm trying to understan
what the Plan2 are referencing. When I plug it in, it asks me to updat
values and then I get #N/A? for the two Plan2s that are referenced afte
the MATCH function.

Dear *Bretai2k*, Good Evening.

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,Sheet2!$B$2:$B$75,0)+1,":$B",MATCH(B2,Sheet2!$B$2:$B$75
0)+1)))

Have a nice day

 

[Bretai2k]

Dear *Bretai2k*, Good Evening.

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,Sheet2!$B$2:$B$75,0)+1,":$B",MATCH(B2,Sheet2!$B$2:$B$75
0)+1)))

Have a nice day.

Thank you so much for your help thus far. I'm still getting #N/A whe
it gets to the Sheet2! portion of each Match function. The actual rang
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 shee
2, cells A2 and B2

 

[Ron Rosenfeld]

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

 

[Mazzaropi]

Thank you so much for your help thus far. I'm still getting #N/A whe
it gets to the Sheet2! portion of each Match function. The actual rang
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 shee
2, cells A2 and B2.

<<<<< *HELP from BRAZIL* >>>>>

Dear *Bretai2k*, Good Morning.

It´s very important always, the user attach here a worksheet with a
example about your question.
It´s quicker, easier and all the answers are effective on the solution.

AT FIRST TIME YOU SAID[/B]:
\"...-ORIGINALLY POSTED BY BRETAI2K
I HAVE 2 DROPDOWNS (A2 AND B2) ON *SHEET 1* THAT BOTH REFERENCE CELLS B
THROUGH B75 ON *SHEET 2*. I WANT TO BE ABLE TO DO A SUM OF ALL CELLS I
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,SH
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 en
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,Sheet1!$B$2:$B$75,0)+1,":$B",MATCH(B2,Sheet1!$B$2:$B
75, 0)+1))

Is this working for you now

 

[Bretai2k]

<<<<< *HELP from BRAZIL* >>>>>

Dear *Bretai2k*, Good Morning.

It´s very important always, the user attach here a worksheet with a
example about your question.
It´s quicker, easier and all the answers are effective on the solution.

AT FIRST TIME YOU SAID[/B]:
\"...-ORIGINALLY POSTED BY BRETAI2K
I HAVE 2 DROPDOWNS (A2 AND B2) ON *SHEET 1* THAT BOTH REFERENCE CELLS B
THROUGH B75 ON *SHEET 2*. I WANT TO BE ABLE TO DO A SUM OF ALL CELLS I
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,SH
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 en
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,Sheet1!$B$2:$B$75,0)+1,":$B",MATCH(B2,Sheet1!$B$2:$B
75, 0)+1))

Is this working for you now?

It still does not appear to be working. It could simply be that I a
not asking the correct question. I'm sorry, I should have added th
spreadsheet in question from the get go and here it is. Basically
want to select an option for Sheet2 A2 (sorry my sheets are name
backwards), also select an option for Sheet2 B2, then have a functio
that looks at A2 and B2 on Sheet 2, then uses A2 as it's start point
and B2 as it's end point for giving a SUM of every item in the list i
Sheet 1 B3 through B78, and give me the result.

Hope this helps you help me .

Again, thank you for all of your help on this

+-------------------------------------------------------------------
|Filename: Co3k-Hero-Exp.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=379
+-------------------------------------------------------------------

 

[Spencer101]

It still does not appear to be working. It could simply be that I a
not asking the correct question. I'm sorry, I should have added th
spreadsheet in question from the get go and here it is. Basically
want to select an option for Sheet2 A2 (sorry my sheets are name
backwards), also select an option for Sheet2 B2, then have a functio
that looks at A2 and B2 on Sheet 2, then uses A2 as it's start point
and B2 as it's end point for giving a SUM of every item in the list i
Sheet 1 B3 through B78, and give me the result.

Hope this helps you help me .

Again, thank you for all of your help on this.

Have a look at the attached, specifically the yellow cell (G6).
Is this what you mean?

+-------------------------------------------------------------------
|Filename: Co3k-Hero-Exp.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=380
+-------------------------------------------------------------------

 

[Bretai2k]

Have a look at the attached, specifically the yellow cell (G6).
Is this what you mean??

Thats exactly what I was looking for, thank you so much! Now time t
start learning how this forumla works .

Thank you all again for your help

+-------------------------------------------------------------------
+-------------------------------------------------------------------