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.

 

[Richard Schollar]

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



Richard

 

[Schokuspokus]

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.

 

[Andrew Poulsom]

Try SUMPRODUCT:

=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))

 

[Schokuspokus]

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)

Example:

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.

 

[Andrew Poulsom]

The SUMPRODUCT didn't work? What result did you get?

 

[Richard Schollar]

Andrew was right (& I was wrong): it should work with SUMPRODUCT.

Question:

Are the values in column AG text or numerics?

I presume A2 holds a numeric date?

 

[Schokuspokus]

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?

 

[Andrew Poulsom]

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))

 

[Schokuspokus]

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.