Date: prev next · Thread: first prev next last
2014 Archives by date, by thread · List index

All functions I tried worked cross-sheet except AVERAGEIF and COUNTIF, so I am assuming this is a 
bug. If AVERAGEIF would work this would be so simple. I am away from home right now but will send 
the formula tomorrow.

Sent from my Verizon Wireless 4G LTE smartphone

-------- Original message --------
From: Brian Barker <> 
Date:2014/01/04  23:12  (GMT-05:00) 
Subject: Re: [libreoffice-users] Calc cross-sheet issue with COUNTIF
  and AVERAGEIF... 

At 19:10 04/01/2014 -0500, Ryan Ashley wrote:
I have developed a spreadsheet which contains a sheet for the cover 
and basic information as well as one sheet for every month. There is 
a cell with a number on every sheet at location H35. I want the 
average of all of those which are not zero on the first sheet. I 
initially tried using "AVERAGEIF(January.H35:December.H35, ">0")", 
but it keeps giving me error 504.

This sounds a bug: AVERAGE() works on a cross-sheet range, so I don't 
see why AVERAGEIF() shouldn't.

I then tried the formula below, ...

Er, I don't see a formula below ...

... which uses COUNTIF, but the part with COUNTIF in it causes a 504 also.

Isn't that also a bug?

At 03:28 05/01/2014 +0200, Paul Steyn wrote:
One way would be to add a second cell to each sheet, say H36, that 
has a simple "IF(H53>0,1,0)", then on the cover sheet your formula 
could be something like
Of course you would need to type in all the actual cell references 
instead of the ellipses.

Since SUM() *does* work on cross-sheet ranges, you could simplify this to

Maybe someone else knows a way to do it using existing functions, ...

Keep watching.

At 21:38 04/01/2014 -0500, Ryan Ashley wrote:
Well I have designed the sheets to be printed at the end of each 
year, so extra cells would not work.

That is no problem: the intermediate cells could be on other sheets 
or simply outside the print range.  Another way of using intermediate 
values would be simply to have a range of twelve cells on your first 
sheet that simply contain =January.H35 and so on.  If preferred, this 
range could be outside your print range - or even 
hidden.  AVERAGEIF() should then work straightforwardly on this range 
of copies.

Is there a reason that AVERAGEIF and COUNTIF will not work with data 
on other sheets?

Not that I can see.

... is there any possible way to do this in LO?

This is a messy workaround, but it appears to work:

I trust this helps.

Brian Barker

To unsubscribe e-mail to:
Posting guidelines + more:
List archive:
All messages sent to this list will be publicly archived and cannot be deleted

To unsubscribe e-mail to:
Posting guidelines + more:
List archive:
All messages sent to this list will be publicly archived and cannot be deleted


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.