Hi, i have created a sheet that uses countif for data of other spreadsheets.
is there a way to retrieve the data without opening all the the source data
files each time?
cheers

Use SUMPRODUCT instead

=SUMPRODUCT(--(book_reference=value))

--
HTH

Bob Phillips

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

"massi" wrote in message
...
Hi, i have created a sheet that uses countif for data of other
spreadsheets.
is there a way to retrieve the data without opening all the the source
data
files each time?
cheers

thanks for your reply but i cannot make it work.

The formula with the countif is the following:

=COUNTIF('[MDR.xls]Drwgs'!$H:$H,"p")

where MDR is the other file, h is the column where i look for the data (the
letter P in this particular occasion)

if I just substitute COUNTIF with SUMPRODUCT it doesn't work....







"Bob Phillips" wrote:

Use SUMPRODUCT instead

=SUMPRODUCT(--(book_reference=value))

--
HTH

Bob Phillips

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

"massi" wrote in message
...
Hi, i have created a sheet that uses countif for data of other
spreadsheets.
is there a way to retrieve the data without opening all the the source
data
files each time?
cheers

=SUMPRODUCT(--(A1:A10="g"))
it works fine if the fomula stays in the same sheet as the data but if the
range A1:A10 is in another spread sheet i cannot find the right sintax for it.


'[fileMDR.xls]Drwgs'!$H:$H

this is how the fomula with the file name would be like if I used countif so
i have tried to copy it in the sumproduct but with no luck

"Bob Phillips" wrote:

Use SUMPRODUCT instead

=SUMPRODUCT(--(book_reference=value))

--
HTH

Bob Phillips

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

"massi" wrote in message
...
Hi, i have created a sheet that uses countif for data of other
spreadsheets.
is there a way to retrieve the data without opening all the the source
data
files each time?
cheers

=SUMPRODUCT(--('[MDR.xls]Drwgs'!$H1:$H1000="p"))

Note that SUMPRODUCT doesn't work with complete columns, you have to specify
a range.

--
HTH

Bob Phillips

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

"massi" wrote in message
...
thanks for your reply but i cannot make it work.

The formula with the countif is the following:

=COUNTIF('[MDR.xls]Drwgs'!$H:$H,"p")

where MDR is the other file, h is the column where i look for the data
(the
letter P in this particular occasion)

if I just substitute COUNTIF with SUMPRODUCT it doesn't work....







"Bob Phillips" wrote:

Use SUMPRODUCT instead

=SUMPRODUCT(--(book_reference=value))

--
HTH

Bob Phillips

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

"massi" wrote in message
...
Hi, i have created a sheet that uses countif for data of other
spreadsheets.
is there a way to retrieve the data without opening all the the source
data
files each time?
cheers

Hi Bob.
sorry to bother you but it still doesn't work. I tried that sintax but it's
not working.
By the way i have noticed that the fomrula would not work for the whole
column H..


I have tried to put an absolute link to the file like
=SUMPRODUCT(--('[N:\FPSO\12. TechDoc\00 KUI
MDR\[MDR.xls]Drwgs'!$H1:$H1000="s"))

but it says that the formula contains one or more invalid references and to
verify that the formula contains a valid path, workbook, range name and cell
reference.

is thre somthing i'm missing?
thanks for your help



"Bob Phillips" wrote:

=SUMPRODUCT(--('[MDR.xls]Drwgs'!$H1:$H1000="p"))

Note that SUMPRODUCT doesn't work with complete columns, you have to specify
a range.

--
HTH

Bob Phillips

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

"massi" wrote in message
...
thanks for your reply but i cannot make it work.

The formula with the countif is the following:

=COUNTIF('[MDR.xls]Drwgs'!$H:$H,"p")

where MDR is the other file, h is the column where i look for the data
(the
letter P in this particular occasion)

if I just substitute COUNTIF with SUMPRODUCT it doesn't work....







"Bob Phillips" wrote:

Use SUMPRODUCT instead

=SUMPRODUCT(--(book_reference=value))

--
HTH

Bob Phillips

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

"massi" wrote in message
...
Hi, i have created a sheet that uses countif for data of other
spreadsheets.
is there a way to retrieve the data without opening all the the source
data
files each time?
cheers

don't know how but i managed to make it work..... i think it was a matter of
spaces and dots in the file name (not my choice..).
anyway, thanks for your time and help. I have one more question pls. it just
about curiosity: the sign -- what is needed for exactly?

cheers





"massi" wrote:

Hi Bob.
sorry to bother you but it still doesn't work. I tried that sintax but it's
not working.
By the way i have noticed that the fomrula would not work for the whole
column H..


I have tried to put an absolute link to the file like
=SUMPRODUCT(--('[N:\FPSO\12. TechDoc\00 KUI
MDR\[MDR.xls]Drwgs'!$H1:$H1000="s"))

but it says that the formula contains one or more invalid references and to
verify that the formula contains a valid path, workbook, range name and cell
reference.

is thre somthing i'm missing?
thanks for your help



"Bob Phillips" wrote:

=SUMPRODUCT(--('[MDR.xls]Drwgs'!$H1:$H1000="p"))

Note that SUMPRODUCT doesn't work with complete columns, you have to specify
a range.

--
HTH

Bob Phillips

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

"massi" wrote in message
...
thanks for your reply but i cannot make it work.

The formula with the countif is the following:

=COUNTIF('[MDR.xls]Drwgs'!$H:$H,"p")

where MDR is the other file, h is the column where i look for the data
(the
letter P in this particular occasion)

if I just substitute COUNTIF with SUMPRODUCT it doesn't work....







"Bob Phillips" wrote:

Use SUMPRODUCT instead

=SUMPRODUCT(--(book_reference=value))

--
HTH

Bob Phillips

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

"massi" wrote in message
...
Hi, i have created a sheet that uses countif for data of other
spreadsheets.
is there a way to retrieve the data without opening all the the source
data
files each time?
cheers

the most asked question in the NGs <vbg

See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation.

--
HTH

Bob Phillips

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

"massi" wrote in message
...
don't know how but i managed to make it work..... i think it was a matter
of
spaces and dots in the file name (not my choice..).
anyway, thanks for your time and help. I have one more question pls. it
just
about curiosity: the sign -- what is needed for exactly?

cheers





"massi" wrote:

Hi Bob.
sorry to bother you but it still doesn't work. I tried that sintax but
it's
not working.
By the way i have noticed that the fomrula would not work for the whole
column H..


I have tried to put an absolute link to the file like
=SUMPRODUCT(--('[N:\FPSO\12. TechDoc\00 KUI
MDR\[MDR.xls]Drwgs'!$H1:$H1000="s"))

but it says that the formula contains one or more invalid references and
to
verify that the formula contains a valid path, workbook, range name and
cell
reference.

is thre somthing i'm missing?
thanks for your help



"Bob Phillips" wrote:

=SUMPRODUCT(--('[MDR.xls]Drwgs'!$H1:$H1000="p"))

Note that SUMPRODUCT doesn't work with complete columns, you have to
specify
a range.

--
HTH

Bob Phillips

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

"massi" wrote in message
...
thanks for your reply but i cannot make it work.

The formula with the countif is the following:

=COUNTIF('[MDR.xls]Drwgs'!$H:$H,"p")

where MDR is the other file, h is the column where i look for the
data
(the
letter P in this particular occasion)

if I just substitute COUNTIF with SUMPRODUCT it doesn't work....







"Bob Phillips" wrote:

Use SUMPRODUCT instead

=SUMPRODUCT(--(book_reference=value))

--
HTH

Bob Phillips

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

"massi" wrote in message
...
Hi, i have created a sheet that uses countif for data of other
spreadsheets.
is there a way to retrieve the data without opening all the the
source
data
files each time?
cheers

thanks a lot.
BR
Massi

"Bob Phillips" wrote:

the most asked question in the NGs <vbg

See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation.

--
HTH

Bob Phillips

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

"massi" wrote in message
...
don't know how but i managed to make it work..... i think it was a matter
of
spaces and dots in the file name (not my choice..).
anyway, thanks for your time and help. I have one more question pls. it
just
about curiosity: the sign -- what is needed for exactly?

cheers





"massi" wrote:

Hi Bob.
sorry to bother you but it still doesn't work. I tried that sintax but
it's
not working.
By the way i have noticed that the fomrula would not work for the whole
column H..


I have tried to put an absolute link to the file like
=SUMPRODUCT(--('[N:\FPSO\12. TechDoc\00 KUI
MDR\[MDR.xls]Drwgs'!$H1:$H1000="s"))

but it says that the formula contains one or more invalid references and
to
verify that the formula contains a valid path, workbook, range name and
cell
reference.

is thre somthing i'm missing?
thanks for your help



"Bob Phillips" wrote:

=SUMPRODUCT(--('[MDR.xls]Drwgs'!$H1:$H1000="p"))

Note that SUMPRODUCT doesn't work with complete columns, you have to
specify
a range.

--
HTH

Bob Phillips

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

"massi" wrote in message
...
thanks for your reply but i cannot make it work.

The formula with the countif is the following:

=COUNTIF('[MDR.xls]Drwgs'!$H:$H,"p")

where MDR is the other file, h is the column where i look for the
data
(the
letter P in this particular occasion)

if I just substitute COUNTIF with SUMPRODUCT it doesn't work....







"Bob Phillips" wrote:

Use SUMPRODUCT instead

=SUMPRODUCT(--(book_reference=value))

--
HTH

Bob Phillips

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

"massi" wrote in message
...
Hi, i have created a sheet that uses countif for data of other
spreadsheets.
is there a way to retrieve the data without opening all the the
source
data
files each time?
cheers