Ask Your Question

countifs() + range in external files

asked 2015-11-16 03:45:23 +0200

cbg.jahn gravatar image

updated 2015-11-17 14:32:37 +0200

Alex Kemp gravatar image

Dear experienced users of Calc:

Error for all *ifs() functions when used in combination of *ifs() + ranges in external files.

Using countifs() as example for illustration.

Do *if() and *ifs() use the same code module for URL parsing?

I have not found a clue in past discussion. Maybe I missed it. I would appreciate advice of where my oversight is.

Thanks. Ray

libreoffice, version error: 504, error in parameter list.

  1. parameter separator: “,” or “;”, no effect on *if(), same result.
  2. UTF-8 + space in sheet name: no effect on *if(), same result.

MS Windows XP, service pack 2, RAM 1 GB

Please refer to the revised ODS files, A44:C47, in the attachment at URL below. .


  1. countifs() error in ODS, in LOO link syntax, range in external file.
  2. countifs() OK in ODS, in LOO link syntax, range anywhere in same file.
  3. countifs() OK in XLS, in MS link syntax, range in self or external file.

Using the same URL syntax, *if() were OK, whereas *ifs() failed.

  1. *if() | *ifs() | source
  2. OK | OK | self file
  3. OK | error | self file, but with file prefix
  4. OK | error | external file

examples (files):

  1. error, LibreOffice + tst analysis.ods + tst source.ods
  2. error, LibreOffice + tst analysis.ods + tst source.xls
  3. OK, MS Office + tst analysis.xls + tst source.xls

examples (URL):

  1. self file, OK: countifs( ‘表 data’.$B$5:$B$20, “>=5000” )
  2. self file, but with file prefix: countifs( ‘file:///b:/tst analysis.ods’#‘表 data’.$B$5:$B$20, “>=5000” )
  3. external file, error: countifs( ‘file:///b:/tst source.ods’#‘表 data’.$B$5:$B$20, “>=5000” )

example files: 2 ods, 2 xls, .

edit retag flag offensive close merge delete


Have you tried what Lupp propose in libreofficeforum?

m.a.riosv gravatar imagem.a.riosv ( 2015-11-16 22:45:19 +0200 )edit

Dear m.a.riosv:

Thanks for following up.

  1. removing UTF-8 and space

no effect, same results. as expected, from programming perspective.

  1. original question

Do *if() and *ifs() use the same code module for URL parsing?

Please refer to "tst analysis.ods", A44:C47. The nature of code bug is self-evident.

Sincerely, Ray

cbg.jahn gravatar imagecbg.jahn ( 2015-11-17 07:18:08 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2015-12-07 19:33:28 +0200

It is quite a problem. Because of it I can not use libre calc and must use MS Excel :( for years.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2015-11-16 03:45:23 +0200

Seen: 133 times

Last updated: Nov 16 '15