Cannot get the total count information from another file

[zjianguk]

Dear Sir,

I tried to use the =COUNTIF("external file ...", "xxx") to get the information of total number of cells equal to xxx in another file. But the result is error.

If I open the external file, then it worked. Can I ask how to use a formula to overcome this problem so that the external file is not needed to open.

Thanks a lot

[Fotis1991]

Try

=sumproduct(("external file ...", "xxx")+0)

[zjianguk]

Try

=sumproduct(("external file ...", "xxx")+0)

Hi, Fotis1991. Sorry I tried your formula a few times, but always resulted in a error. Can you please have a second look? I am sure it will be fine to work.

[JosephP]

it oughta be

=sumproduct(("external file ..."="xxx")+0)

[Fotis1991]

My bad... Thanks Joseph.

[JosephP]

no problem :-)

[zjianguk]

Thanks for the great magic, Fotis1991 and Joseph.

[Fotis1991]

.........................

[zjianguk]

Sorry to have to come back. I found this approach works fine in Excel 2007, but it did not work in Excel 2003

Can I ask how to get the total count information from another file in Excel 2003?

[JosephP]

it oughta work-but you can't use whole column references in 2003

[zjianguk]

Haha, Joseph. Yes. It worked now with the row numbers specified I appreciate it.