How can I create a dynamic excel range for a formula?

Question

I'm trying to create an Excel formula that will create a sum whose range is based on the contents of a different cell. So for example I have the following data:

The "Yes's" are based on criteria from another part of the Excel sheet, and will not always be in the same places. I want to be able to sum the values "between" the "Yes's", so in this case C9=SUM(B1:B9) and C21=SUM(B10:B21). Any ideas?

"Yes" is telling me that there is a corresponding value in another sheet, and it's between these values that I need to count instances another element. — Jack Vawdrey
– Jack Vawdrey, Commented Sep 12, 2016 at 15:48
Yes, but you are not including row(10) in the first but including row(21) in the second as the last row. I assumed in my answer that the "Yes" should actually be in the row under the last value to be summed. — Scott Craner
– Scott Craner, Commented Sep 12, 2016 at 15:49
Yeah, row 21 should not be included in the second summation. This is just an example, the values could continue on — Jack Vawdrey
– Jack Vawdrey, Commented Sep 12, 2016 at 16:03

Scott Craner · Accepted Answer · 2016-09-12 15:42:56Z

2

Put this formula in C1:

=IF(A2="Yes",SUM($B$1:INDEX(B:B,ROW()))-SUM($C$1:INDEX(C:C,ROW()-1)),"")

Then copy down.

answered Sep 12, 2016 at 15:42

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How can I create a dynamic excel range for a formula?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related