SUM columns based on a dynamic value

Andrew B. Boston

Hello, I'm trying to build a formula that will SUM a dynamic range of columns based on a value. The application is for capacity planning (hours per week) and the sheet has a column for every week of the year (see image). I want to SUM the remaining hours as each week passes. I know the week number where I want the range to start the SUM from, but can't figure out the formula. Any advice is greatly appreciated.

Andrew B. Boston

Hi Paul, thanks for jumping in. The answer to your question is no. The sheet in question is a capacity plan (planned hours per week) and we don't want to zero (null) them after that week has passed. What we're trying to calculate is the total "future" hours remaining in the plan from a specific week forward. So, in week 7, I want to know the total hours in the capacity plan from weeks 8 to 52. Hope this helps. Thanks.

Adam Murphy

Need more information, what value are you basing it on? Have you looked at the SUMIF formula? It does what you are describing (I think).

Andrew B. Boston

Hi Adam, thanks for the reply. I have another column that has the week number to start the SUM range from (this is the value that changes), with the SUM range ending with the week 52 column. Here is a run through:

• This runs approximately once a week
• There is another column (not in the image) that has the week# (column) to start the SMU range from.
• The SUM I am trying to perform would start with that week# (column) and include all remaining weeks.

Let me know if this addresses your question. Thanks again for the support!

Andrew B. Boston

Hello,

Does anyone have any feedback on this challenge? Appreciate any guidance you may have. Thanks.

Paul Newcome

Would it by chance be any column that isn't blank?

Andrew B. Boston

Hi Paul, thanks for jumping in. The answer to your question is no. The sheet in question is a capacity plan (planned hours per week) and we don't want to zero (null) them after that week has passed. What we're trying to calculate is the total "future" hours remaining in the plan from a specific week forward. So, in week 7, I want to know the total hours in the capacity plan from weeks 8 to 52. Hope this helps. Thanks.