sumif formula result shows only when source file is open

Schokuspokus

New Member
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?

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:

Which must be confirmed with Ctrl+Shift+Enter

Richard

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.

Try SUMPRODUCT:

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.

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

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?

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?

What does the first part return?

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.

Replies
15
Views
613
Replies
5
Views
268
Replies
3
Views
872
Replies
2
Views
511
Replies
9
Views
1K

1,216,129
Messages
6,129,055
Members
449,484
Latest member
khairianr

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.

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

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