sumif formula result shows only when source file is open

Schokuspokus

New Member
Joined
Oct 25, 2005
Messages
9
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

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

New Member
Joined
Oct 25, 2005
Messages
9
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:
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

New Member
Joined
Oct 25, 2005
Messages
9

ADVERTISEMENT

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.
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

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

New Member
Joined
Oct 25, 2005
Messages
9
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

New Member
Joined
Oct 25, 2005
Messages
9
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,047
Messages
5,639,767
Members
417,110
Latest member
RayClarke

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 MrExcel.com.
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 "mrexcel.com".
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
Top