Hey Biff,

thanks again. Sorry about the formula I gave you, yes you were right there
was a mistake (should have been $A$2:$A$27)

I tried your SUMPRODUCT formula and it worked. But were would I insert the
full file path in the formula for it to work? June.xls can be found in:

E:\OT Sheet\June.xls

Thanks,
Matt


"Biff" wrote:

=SUMIF([June.xls]Sheet1!$A$27,A4,[June.xls]Sheet1!$G$2:$G$27)

That formula doesn't make any sense. Are you sure that formula is actually
doing what you want it to?

The first argument should be a range of cells equal in size to
Sheet1!$G$2:$G$27. It should look like this:

=SUMIF([June.xls]Sheet1!$A$2:$A$27,A4,[June.xls]Sheet1!$G$2:$G$27)

Sumproduct will not work with unequal range sizes. This is the Sumproduct
version:

=SUMPRODUCT(--([June.xls]Sheet1!$A$2:$A$27=A4),[June.xls]Sheet1!$G$2:$G$27)

You'll have to put in the full path for it to work when June.xls is closed.

Biff

"mpenkala" wrote in message
...
Hi Biff,

I've never used SUMPRODUCT before so I'll need some help using it. Here's
the SUMIF formula I was using...

=SUMIF([June.xls]Sheet1!$A$27,A4,[June.xls]Sheet1!$G$2:$G$27)


Thanks for your help,
Matt


"Biff" wrote:

Any ideas or comments??

Don't use SUMIF. Use SUMPRODUCT instead.

If you're not sure how to use Sumproduct post your Sumif formula and
we'll
translate it into Sumproduct.

Biff

"mpenkala" wrote in message
...
Hi all,

So I've got a new problem. Kinda like my last one, but a little easier
(I
hope).

I currently have a main workbook called 'Dept OT' and Sheet1 has been
renamed 'Comms June-Dec2006'. On this sheet I have the following:
Column 'A' - Employee ID#
Column 'B' - Employee Name
Column 'C' - Total OT (Monthly June)
Column 'D' - Total OT (Yearly)
Column 'E' - Percentage OT Monthly
Column 'F' - Percentage OT Yearly

I then have a empty column and repeat the process for July, Aug, etc...
I use a SUMIF function in ColumnC to find the the information needed.
The
information is located in a diffenent workbook called 'JuneOT' (JulyOT
for
July, AugOT for August... etc).

JuneOT is simply names, ID#, and OT hours worked.
Column 'A' - ID #
Column 'B' - name
Column 'C' - Date
Column 'D' - Hours worked

My SUMIF simply looks for the ID#, matches it with the ID# in the
DeptOT
workboot, then sums the hours worked if a match occurrs. The function
works
great - that's not my problem - it's after I save and close excel...

Once I close excel and then try to open DeptOT I get the following
message:

"This workbook contains links to other data sources.
-If you udate the links, Excel will attempt to retreve the latest data.
-If you don't update the links, Excel will use the previous
information.

If I try and Update I get 2 possible problems.

1 - The workbook contain one or more links that cannot be updated.
2 - All of my SUMIF cells show #VALUE until I actually open up the
JuneOT,
JulyOT...etc. and then close them again. Once this is done, DeptOT
looks
and
works fine.

Any ideas or comments?? I'm lost on this. I even tried to create a
macro
that would open up JuneOT, JulyOT, AugOT, etc and then close them
again,
but
it always closes DeptOT as well... so I guess I'm stuck.

Thanks,
Matt