Thanks!

Terri

"Frank Kabel" wrote:

Hi
sorry, my fault. I just copied your formula. SUMPRODUCT can't work with
ranges such as $S:$S. So change the formula to:
=(SUMPRODUCT(--('C:\mws\Tracking
Files\[Tue106.xls]TP_TRK_Query'!$S1:$S1000=3),'C:\mws\Tracking
Files\[Tue106.xls]TP_TRK_Query'!$G1:$G1000)/3*0.7+(SUMPRODUCT(--('C:\mws\Tracking
Files\[Tue106.xls]TP_TRK_Query'!$S1:$S1000=3),'C:\mws\Tracking
Files\[Tue106.xls]TP_TRK_Query'!$P1:$P1000))/3)*0.7

For an explanation see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards
Frank Kabel
Frankfurt, Germany
"Terri Miller" schrieb im
Newsbeitrag ...
I tried inputting the formula you suggested and I get a #Num! error. I
have
never used SUMPRODUCT before. Does the formula you suggest only multiple
column G by .7/3 when column S has a three in it?

Is there any additional suggestions you may have as to the changing of my
approach to this problem?

"Frank Kabel" wrote:

Hi
SUMIF won't work on closed workbooks. If you open a file saved in an
older
version Excel 2002/2003 to re-calculate all cells (and fails with that).
Workaround: Use SUMPRODUCT instead. e.g. for your formula:
=(SUMPRODUCT(--('C:\mws\Tracking
Files\[Tue106.xls]TP_TRK_Query'!$S:$S=3),'C:\mws\Tracking
Files\[Tue106.xls]TP_TRK_Query'!$G:$G)/3*0.7+(SUMPRODUCT(--('C:\mws\Tracking
Files\[Tue106.xls]TP_TRK_Query'!$S:$S=3),'C:\mws\Tracking
Files\[Tue106.xls]TP_TRK_Query'!$P:$P))/3)*0.7

--
Regards
Frank Kabel
Frankfurt, Germany
"Terri Miller" <Terri schrieb im
Newsbeitrag ...
Thanks Frank. Here it is:

=(SUMIF('C:\mws\Tracking
Files\[Tue106.xls]TP_TRK_Query'!$S:$S,3,'C:\mws\Tracking
Files\[Tue106.xls]TP_TRK_Query'!$G:$G)/3*0.7+(SUMIF('C:\mws\Tracking
Files\[Tue106.xls]TP_TRK_Query'!$S:$S,3,'C:\mws\Tracking
Files\[Tue106.xls]TP_TRK_Query'!$P:$P))/3)*0.7

"Frank Kabel" wrote:

Hi
as always: please post your formula

--
Regards
Frank Kabel
Frankfurt, Germany
"How do I eliminate a #value! error?" <How do I eliminate a #value!
schrieb im Newsbeitrag
...
I have a somewhat complicated formula in that it refers to a separate
spreadsheet. The formula works fine, but the problem seems to be
that
until
I open the referred to spreadsheet, I get a #value! error. I know
that
I
have used this type of formula before and didn't need to open the
spreadsheet
to get my answers. Why is it doing it now?

It might help to let you know that the spreadsheet referred to was
created
by access as a query, but I don't see how that changes it as I save
the
query
as an excel spreadsheet.

Any help would be greatly appreciated, as the spreadsheet generated
refers
to 7 different files (Sunday - Monday) and I need to creat a new
spreadsheet
for 8 different locations, so I would have to open over 56 files to
get
these
spreadsheets to the stores.