Problem with SumProduct
 

Hi,

I am in trouble and need your help.....

I have an excel sheet that contains data for June and July. The data cannot
be sorted and contains one day may be more than once.

For instance
After using countif
Dates Values
Dates

No. of times date gets repeated
6/5/06 0
6/5/06 2
6/4/06 1
6/4/06 1
6/5/06 2
6/8/06 4
6/8/06 2
7/8/06 4
7/8/06 1
6/8/06 1
Using countif, I am able to count the no. of times these dates are repeating
(in our example - 6/5/06 -2, 6/8/06 -1)

You can see that there are some values besides different date, now what I
need is to calulate how many times the value is above 2 for a particular day
i.e 6/8/06 - 1. If day wise is not possible, please let me know if monthwise
wld be possible.

I tried sumproduct but the problem is the formula is in one file and I have
a data in another file.

Both the documents are linked and are at shared drive but the problem is when
anybody other than me tries to open the file they receive a pop up box
"Whether you to Update or Dont Update". Choosing either of the option changes
the formula automatically. The new formula contains the path of the file but
still it does'nt works

Please Help ASAP

Thanks,
Dinesh Advani

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200607/1

Problem with SumProduct
 

Something like

=SUMPRODUCT(--('K:\My
Documents\Spreadsheets\[XXXX.xls]Requisitions'!$A$2:$A$24=--"2006-08-06"),
--('K:\My Documents\Spreadsheets\[XXXX.xls]Requisitions'!$B$2:$B$242))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"dinadvani via OfficeKB.com" <u22798@uwe wrote in message
news:63e8abe58cd5c@uwe...
Hi,

I am in trouble and need your help.....

I have an excel sheet that contains data for June and July. The data
cannot
be sorted and contains one day may be more than once.

For instance
After using countif
Dates Values
Dates

No. of times date gets repeated
6/5/06 0
6/5/06 2
6/4/06 1
6/4/06 1
6/5/06 2
6/8/06 4
6/8/06 2
7/8/06 4
7/8/06 1
6/8/06 1
Using countif, I am able to count the no. of times these dates are
repeating
(in our example - 6/5/06 -2, 6/8/06 -1)

You can see that there are some values besides different date, now what I
need is to calulate how many times the value is above 2 for a particular
day
i.e 6/8/06 - 1. If day wise is not possible, please let me know if
monthwise
wld be possible.

I tried sumproduct but the problem is the formula is in one file and I
have
a data in another file.

Both the documents are linked and are at shared drive but the problem is
when
anybody other than me tries to open the file they receive a pop up box
"Whether you to Update or Dont Update". Choosing either of the option
changes
the formula automatically. The new formula contains the path of the file
but
still it does'nt works

Please Help ASAP

Thanks,
Dinesh Advani

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200607/1

Problem with SumProduct
 

Hi Bob,

I have a problem using this formula, since the excel sheets are in commondata
folder. I only have

=SUMPRODUCT(--(\\ntbom\\DReports\Article\[xxx.xls]Report'!$A$2:$A$24=--"2006-
08-06"),
--(\ntbom\\DReports\Article\[xxx.xls]Report'!$B$2:$B$242))

Also I have used countif formula and that also have the same problem.

Need your help...

Thanks,
DA

Bob Phillips wrote:
Something like

=SUMPRODUCT(--('K:\My
Documents\Spreadsheets\[XXXX.xls]Requisitions'!$A$2:$A$24=--"2006-08-06"),
--('K:\My Documents\Spreadsheets\[XXXX.xls]Requisitions'!$B$2:$B$242))

Hi,

[quoted text clipped - 40 lines]
Thanks,
Dinesh Advani

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200607/1

Problem with SumProduct
 

Open the xxx.xls file while you create the formulas.

After you get it to work with the file open, you can close that xxx.xls file and
excel will adjust the formula.

You'll see the correct syntax then.

"dinadvani via OfficeKB.com" wrote:

Hi Bob,

I have a problem using this formula, since the excel sheets are in commondata
folder. I only have

=SUMPRODUCT(--(\\ntbom\\DReports\Article\[xxx.xls]Report'!$A$2:$A$24=--"2006-
08-06"),
--(\ntbom\\DReports\Article\[xxx.xls]Report'!$B$2:$B$242))

Also I have used countif formula and that also have the same problem.

Need your help...

Thanks,
DA

Bob Phillips wrote:
Something like

=SUMPRODUCT(--('K:\My
Documents\Spreadsheets\[XXXX.xls]Requisitions'!$A$2:$A$24=--"2006-08-06"),
--('K:\My Documents\Spreadsheets\[XXXX.xls]Requisitions'!$B$2:$B$242))

Hi,

[quoted text clipped - 40 lines]
Thanks,
Dinesh Advani

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200607/1

--

Dave Peterson

Problem with SumProduct
 

Hi,

This doesn't work for me please help. Everytime I have first open the file
that has the data else the formula changes and it not longer is linked. The
data does not gets auto uploaded.

Please help

Thanks,
DA

Dave Peterson wrote:
Open the xxx.xls file while you create the formulas.

After you get it to work with the file open, you can close that xxx.xls file and
excel will adjust the formula.

You'll see the correct syntax then.

Hi Bob,

[quoted text clipped - 27 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200607/1


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200607/1

Problem with SumProduct
 

That's not usual behavior for excel.

Maybe you could start excel in safe mode:
close excel
windows start button|Run
excel /safe

and file|open your two workbooks to see if that helps.

"dinadvani via OfficeKB.com" wrote:

Hi,

This doesn't work for me please help. Everytime I have first open the file
that has the data else the formula changes and it not longer is linked. The
data does not gets auto uploaded.

Please help

Thanks,
DA

Dave Peterson wrote:
Open the xxx.xls file while you create the formulas.

After you get it to work with the file open, you can close that xxx.xls file and
excel will adjust the formula.

You'll see the correct syntax then.

Hi Bob,

[quoted text clipped - 27 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200607/1


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200607/1

--

Dave Peterson