Sum Product or Count If from Another File

Hi,

I'm trying to use a Sumproduct or Countif that will find a certain criteria between specified dates.

By itself or within the same file, the function works well.

Now, trying to extract data from another file in which I'm not permitted to modify, I'm getting a "###" result, instead of a numerical value.

How could I modify the following or have a function that will give me desired results?


=SUMPRODUCT(--('[ABC.xlsx]Issue Tracking'!$F$4:$F$10000>=E21),--('[ABC.xlsx]Issue Tracking'!$F$4:$F$10000<=E27),--'[ABC.xlsx]Issue Tracking'!$L$4:$L$10000=$G$4)

E21 = 09/14/12
E27 = 09/27/12
G4 = XYZ



Thanks

Hi MyCon,

When you close the file abc.xls, I believe full file path should be there for file abc.xls .. check and confirm. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

Hi Dilpandey,

Thanks for responding. What do you mean close the file? Wouldn't I need the file open to "see it" so the function can find appropriate columns & so forth?

I'll try & let you know what happens shortly....

I mean.. did you typed the below portion of formula manually ?

[ABC.xlsx]Issue Tracking'!$F$4:$F$10000

Regards,
DILIPandey

<click on below 'star' if this helps>

Hi Dilpandey,

As suspected, closing the ABC.xls file didn't do anything or change my results.

Ok.. but after closing the ABC.xls.. did you find the complete path of ABC.xls in the formula?

Regards,
DILIPandey

<click on below 'star' if this helps>

Yes - the path is still there.

Okay.. that's interesting.. can you upload both the files ..?? thanks

Regards,
DILIPandey


<click on below 'star' if this helps>

Hi Dilpandey & Others,

As requested, I uploaded 2 (modified) examples files. Hopefully, I didn't modify too much to corrupt the functions that were already working, but I probably did.

File 1: The Source File: ABC.xlsx

-- This file is suppose to be the raw data extracted from a PDM / PLM application & is not suppose to be touched or modified in anyway.

File 2: XYZ_v1 - The tabulation metrics file

-- As you see, I've shown a portion of my tabulation metrics table. In it, there are a series of functions that is suppose to reference the source file, ABC.xlsx & its various columns.

In most instances, I'm trying to extract data between specified duration - weekly, then drag that function across the table.


In row 31, highlighted in Yellow, I need assistance with.

As mentioned, the function may be more corrupted as the original because part of it is still referencing the real source file. If possible, ignore these areas for now.

The intent with the function in Cell D31 is:

Between dates, 09/7/12 to 09/13, it will find & sum up or count the total that company ABC closed an issue, from the Date Closed Column - Column H from the ABC.xlsx file.

Looks like I need to upload files separately - check next post.



Thanks

Hmm? I can't upload the source file, ABC.xlsx for some reason. Perhaps, it's too large & I don't have time at the moment to modify it.

Hopefully, I have the objective defined well enough here though & someone out here can still assist.



Thanks