sumif formula result shows only when source file is open

[Schokuspokus]

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.

 

[Andrew Poulsom]

What does:

=ISNUMBER(A2)

return? And what does it return when applied to your concatenated cell?

By the way should the concatenation formula be?

=CONCATENATE(E2;".";D2;".";C2)

 

[Schokuspokus]

For A2 I receive true
For AG2 I receive false

How do I format AG2 to produce also a true for the number check

You are correct with the formula.

Everything I do is with a german version of Excel so I have to find the English equivalent. For example a ; in German is a , in English.

 

[Andrew Poulsom]

Try formatting A2 as text before entering the date.

 

[Schokuspokus]

It WOOOOORRRKKKS!!!

Thank you very much for your help!