sumif formula result shows only when source file is open


New Member
Oct 25, 2005
The following formula only shows a result in the cell if the source data table is opened. It shows #value! until the source file is oped. Is there any way around it?

=sumif('S:\abteilungen\Kundencenter_LTG\reporting\bla\Statistiken\[Masterdatei bla.xls]Tagesaddition'!$AG$2:$AG$2639,"="&A2,'S:\abteilungen\Kundencenter_LTG\reporting\bla\Statistiken\[Masterdatei bla.xls]Tagesaddition'!$AE$2:$AE$2639)/(countif('S:\abteilungen\Kundencenter_LTG\reporting\bla\Statistiken\[Masterdatei bla.xls]Tagesaddition'!$AG$2:$AG$2639,"="&A2))

Thanks for your help. :(

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
You can't use SUMIF/COUNTIF or SUMPRODUCT if you want it to display values when the externally referenced file is closed, but you can use array formulas.

for example:

=sum(if('S:\abteilungen\Kundencenter_LTG\reporting\bla\Statistiken\[Masterdatei bla.xls]Tagesaddition'!$AG$2:$AG$2639=A2,'S:\abteilungen\Kundencenter_LTG\reporting\bla\Statistiken\[Masterdatei bla.xls]Tagesaddition'!$AE$2:$AE$2639))/sum(if('S:\abteilungen\Kundencenter_LTG\reporting\bla\Statistiken\[Masterdatei bla.xls]Tagesaddition'!$AG$2:$AG$2639=A2,1,0))

Which must be confirmed with Ctrl+Shift+Enter

Upvote 0
Turn sumif formula into an array formula

Would you get me in the right direction turning my formula into an array formula. Would be my first. :biggrin: :biggrin: :biggrin:
Upvote 0

=SUMPRODUCT(('S:\abteilungen\Kundencenter_LTG\reporting\bla\Statistiken\[Masterdatei bla.xls]Tagesaddition'!$AG$2:$AG$2639=A2)*('S:\abteilungen\Kundencenter_LTG\reporting\bla\Statistiken\[Masterdatei bla.xls]Tagesaddition'!$AE$2:$AE$2639))/SUMPRODUCT(--('S:\abteilungen\Kundencenter_LTG\reporting\bla\Statistiken\[Masterdatei bla.xls]Tagesaddition'!$AG$2:$AG$2639=A2))
Upvote 0
Didn't work

It didn't work for me.

What I want to do with an array formula (external data source):

find the Date in AG2:AG2639 maching the date in A2 8multiple finds possible)

give me the average after

suming up the corresponding value out every a match found in table AG2:AG2639 (the corresponding values are found in Table Ae2:AE2639)


A2 = 1.Nov.2005

match at AG2, AG45 and AE2600 (also 1.Nov.2005)

Add the corresponding values out of that row AE2 = 10, AE45=90, AE=2600=20

Calculate the avarage (as a formula it woul be 120/3=40

The result for that cell is 40.

This needs to work with data from an external data source.

The sumif formula worked only when the source table was open.
Upvote 0
Andrew was right (& I was wrong): it should work with SUMPRODUCT.


Are the values in column AG text or numerics?

I presume A2 holds a numeric date?
Upvote 0
Yes A2 holds a numeric date. 1.11.2005

SUMPRODUCT dives me #NAME? as a result.

I thought the problem might have been that I needed to put the German sumproduct in there, which I did(SUMMENPRODUKT) . Result #DIV/0!

That shouldn't be because the the values for the 1.Nov.2005 in column AE are: 100.0% (15 times on 1.Nov.2005)

Any idea why?
Upvote 0
What does the first part return?

=SUMPRODUCT(('S:\abteilungen\Kundencenter_LTG\reporting\bla\Statistiken\[Masterdatei bla.xls]Tagesaddition'!$AG$2:$AG$2639=A2)*('S:\abteilungen\Kundencenter_LTG\reporting\bla\Statistiken\[Masterdatei bla.xls]Tagesaddition'!$AE$2:$AE$2639))
Upvote 0
I have done some testing and found that your formula works. Thanks you.

However it only works if I manually enter the dates into cell A2 and Ag2:AG2639

Your formula doesn't work because I use a formula the put together the date in AG2:AG2639 and it does not except the dates in AG2:AG2639 as a match.

The formula in AG2 for example is: =concatenate(E2,".",D2;",";C2)
where C2 is the year D2 the month and E2 the day

showing 1.11.2005

This however is not seen as the manually entered date of 1.11.2005 in A2.

Is there anyway that I can make Excel see them as a match?

This would solve the last bit of the problem.

And thanks for your help.
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back