Sum with Dynamic Range on a Closed Workbook

**david0985** · 05-07-2018, 07:12 PM

Hi - i'm looking to create a sum with a dynamic range on a closed workbook.

If the workbook is open, i typically just use a formula like:
=SUM(first value : INDEX(sum array, month number))
which will sum from January (first value, or month one) to whatever input i have typed into as the "month number"

However, this formula only works if the workbook is open. If it's closed, i get a #REF error.

I didn't expect this as INDEX (not combined with a SUM function) works on closed books and INDEX with MATCH does as well.

Using the CHOOSE function also returns #REF.

Any creative workarounds?

Thanks!

**NeedForExcel** · 05-08-2018, 12:13 AM

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

**shukla.ankur281190** · 05-08-2018, 02:09 AM

You wish to create a dynamic sum on closed workbook? If you suppose to achieve you target through index and match on closed workbook, you can't. Basically Index and match are not capable to do so but sumproduct having ability to perform the result.

=SUMPRODUCT((CriteriaRANGE1=Criteria1)*(CriteriaRANGE2=Criteria2)*(SUMRANGE)..

If you can attach you sample data you will get easy and exact solution very fast.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

**david0985** · 05-08-2018, 10:32 AM

Here is a sample workbook. It is of course very simple to do if it's all in one workbook, but the trick is that it's in two, and one will always be closed. Is there a way to do a sum like this on a closed workbook? Thank you.