[libreoffice-users] To sum only those values in a range that are between two number

2011-12-28 Thread Csanyi Pal 
Hi,

I'm trying to sum only those values in a range that are in the same time
greater than 25 and lower or equal than 384. 

How can I do that?

-- 
Regards, Pal


-- 
For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Re: [libreoffice-users] To sum only those values in a range that are between two number

2011-12-28 Thread Mirosław Zalewski 
On 28/12/2011 at 15:18, Csanyi Pal csanyi...@gmail.com wrote:
 
 I'm trying to sum only those values in a range that are in the same time
 greater than 25 and lower or equal than 384.
 
 How can I do that?

You can use extra column.

Let's say in column A you have some numbers.
In column B enter formula:
=IF(AND(A124;A1=384);A1)
You see that little black square in lower right corner of cell border? When 
you hover it, your mouse pointer will change into cross. Just grab that square 
and drag it all way down column B to place where number in column A stops. 
This will copy formula from cell B1 to B{number}, changing it's reference 
number in column A.
Now you can simply use SUM function on column B.

One of disadvantages of this method is that AVERAGE function will not work 
correctly. If you need average, you can use something like this:
=SUM(B1:B12)/COUNTIF(B1:B12; 0)

Hopefully there is simpler method someone will point out.
-- 
Best regards
Mirosław Zalewski

-- 
For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Re: [libreoffice-users] To sum only those values in a range that are between two number

2011-12-28 Thread Regina Henschel 

Hi,

Csanyi Pal schrieb:

Hi,

I'm trying to sum only those values in a range that are in the same time
greater than 25 and lower or equal than 384.

How can I do that?



=SUMIF(range;=384) - SUMIF(range;=25)

Kind regards
Regina

--
For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted