I have very strange behaviour (problem): I have to get sum of data from source workbook, cells E1 to E12 accordingly to the month of the year. (for example March SUM should be E1:E3...)

Works both ways (opened and closed source workbook):

=SUM([FLUTING.xlsm]ZBIRNA!E1:E3))

Works only when sourceWB is opened:

=SUM([FLUTING.xlsm]ZBIRNA!E1:INDEX([FLUTING.xlsm]ZBIRNA!E3))

The first case would be OK, but I need that 2.argument to be variable(month dependant) which would be referenced to month number cell in destination Wbook where this formula is at. Which I could't achive!

In second case I achived to integrate varible (2.argument) in INDEX funcion like this:

=SUM([FLUTING.xlsm]ZBIRNA!E1:INDEX([FLUTING.xlsm]ZBIRNA!E:E;'Sheet1'!$F$4))

"Sheet1 F4" holds month number.

Now this, for some reason, works only when Source WB is open, and when it's closed works only for first 2 months; sums january:january(E1:E1) and january:february(E1:E2), but when range stretches from E1:E3 or more, it returns #REF! error! (try for your self on 2 different WBs)

Am I doing something wrong with these formulas? Is there any other way(formulas) to achieve this?!?

I have very strange behaviour (problem): I have to get sum of data from source workbook, cells E1 to E12 accordingly to the month of the year. (for example March SUM should be E1:E3...)

Works both ways (opened and closed source workbook):

=SUM([FLUTING.xlsm]ZBIRNA!E1:E3))

Works only when sourceWB is opened:

=SUM([FLUTING.xlsm]ZBIRNA!E1:INDEX([FLUTING.xlsm]ZBIRNA!E3))

The first case would be OK, but I need that 2.argument to be variable(month dependant) which would be referenced to month number cell in destination Wbook where this formula is at. Which I could't achive!

In second case I achived to integrate varible (2.argument) in INDEX funcion like this:

=SUM([FLUTING.xlsm]ZBIRNA!E1:INDEX([FLUTING.xlsm]ZBIRNA!E:E;'Sheet1'!$F$4))

"Sheet1 F4" holds month number.

Now this, for some reason, works only when Source WB is open, and when it's closed works only for first 2 months; sums january:january(E1:E1) and january:february(E1:E2), but when range stretches from E1:E3 or more, it returns #REF! error! (try for your self on 2 different WBs)

Am I doing something wrong with these formulas? Is there any other way(formulas) to achieve this?!?

• excel
• excel-formula
• excel-2019

• 1 Also, where is the MATCH() function you have used? Don't see without that don't think its going to create the required array for the sum. – Mayukh Bhattacharya Commented Mar 18 at 13:12
• well I used to put in MATCH function also, same thing happened. It seems it's not problem with INDEX or MATCH, Simple SUM function acts the same. =SUM([FLUTING.xlsm]ZBIRNA!E29:[FLUTING.xlsm]ZBIRNA!E31) – Jelovac Maglaj Commented Mar 18 at 13:22
• 1 @BigBen Sir, re-opened it, saw OP has serious issues in solving this. But shouldn't SUM() work even if the workbook is closed. SUMPRODUCT() does it. – Mayukh Bhattacharya Commented Mar 19 at 12:49
• 1 @MayukhBhattacharya I agree, I'd expect SUM to work but it clearly doesn't. – BigBen Commented Mar 19 at 12:50
• 1 Similar, unanswered question – BigBen Commented Mar 19 at 13:21

1 Answer 1

As the first value to be included in the sum is in row 1 it seems that the only criteria needed is to designate the last row so that all values down to that row will be summed.

I got this to work with both workbooks open:

=SUMPRODUCT((ROW([FLUTING.xlsm]ZBIRNA!E:E)<=F4)*([FLUTING.xlsm]ZBIRNA!E:E))

When the FLUTING workbook was closed the full file path became included in the formula and when the value in cell F4 was changed the formula still worked.

Note that this was done using the 365 version so there is a possibility that it will not work in the 2019 version, but at least it may be worth testing.