[libreoffice-users] Re: Searching numbers between two values
No, I know this function before, and I didn't found your message some twelve hours before. Was it in this threed posted by You? Yes, I want to exclude 20 and 60 in this example, and I know for '=' or '=' options. Thank you for the advice about third parameter of SUMIF() so now I deleted those third parameters in this file, and it works. Thank you for your help. - Best Regards from Pál -- View this message in context: http://nabble.documentfoundation.org/Searching-numbers-between-two-values-tp4061491p4061594.html Sent from the Users mailing list archive at Nabble.com. -- To unsubscribe e-mail to: users+unsubscr...@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
[libreoffice-users] Re: Searching numbers between two values
Hi Miguel Ángel, thank you for advices. - Best Regards from Pál -- View this message in context: http://nabble.documentfoundation.org/Searching-numbers-between-two-values-tp4061491p4061602.html Sent from the Users mailing list archive at Nabble.com. -- To unsubscribe e-mail to: users+unsubscr...@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
[libreoffice-users] Re: Searching numbers between two values
Using COUNT()/COUNTIF()/COUNTIFS() SUM()/SUMIF()/SUMIFS() as array or
SUMPRODUCT() must be the way, but seems it is no possible.
Using INDIRECT() combined with ROW() to generate the address do the trick.
As it is an array formula, Ctrl+Shift+Enter (not only enter) after write the
formula without braces.
For count:
{=SUM(IF(INDIRECT(Num
ROW($A$1:$A$3).$A$1)*(CURRENT()20)*(CURRENT()60)0;1;0))}
For sum:
{=SUM(INDIRECT(Num
ROW($A$1:$A$3).$A$1)*(CURRENT()20)*(CURRENT()60))}
CURRENT() function is very useful in this case because avoids repetition of
INDIRECT() function to do the comparisons.
BetweenTwoNumbers.ods
http://nabble.documentfoundation.org/file/n4061598/BetweenTwoNumbers.ods
Miguel Ángel.
--
View this message in context:
http://nabble.documentfoundation.org/Searching-numbers-between-two-values-tp4061491p4061598.html
Sent from the Users mailing list archive at Nabble.com.
--
To unsubscribe e-mail to: users+unsubscr...@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
[libreoffice-users] Re: Searching numbers between two values
Yes, this helps. Thank you! - Best Regards from Pál -- View this message in context: http://nabble.documentfoundation.org/Searching-numbers-between-two-values-tp4061491p4061513.html Sent from the Users mailing list archive at Nabble.com. -- To unsubscribe e-mail to: users+unsubscr...@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
[libreoffice-users] Re: Searching numbers between two values
Brian Barker wrote
Try:
=COUNTIF('Num 1'.A1:Z99;20)-COUNTIF('Num 1'.A1:Z99;=60)
=COUNTIF('Num 2'.A1:Z99;20)-COUNTIF('Num 2'.A1:Z99;=60)
=COUNTIF('Num 3'.A1:Z99;20)-COUNTIF('Num 3'.A1:Z99;=60)
moreover, I don't know how to create fourth function.
Easy:
=J1+J3+J5
(!)
I trust this helps.
Yes, this helps.
-
Best Regards from
Pál
--
View this message in context:
http://nabble.documentfoundation.org/Searching-numbers-between-two-values-tp4061491p4061514.html
Sent from the Users mailing list archive at Nabble.com.
--
To unsubscribe e-mail to: users+unsubscr...@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
[libreoffice-users] Re: Searching numbers between two values
Now I'm searching the way how to SUM those numbers ( between 20 and 60 ) finded on sheets Num1, Num2, Num 3? BetweenTwoNumbers.ods http://nabble.documentfoundation.org/file/n4061519/BetweenTwoNumbers.ods I uploaded the file again, because I changed it a little. I changed it because I search for a function which I can put in to just one cell of the 'between 20-60' sheet. - Best Regards from Pál -- View this message in context: http://nabble.documentfoundation.org/Searching-numbers-between-two-values-tp4061491p4061519.html Sent from the Users mailing list archive at Nabble.com. -- To unsubscribe e-mail to: users+unsubscr...@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] Re: Searching numbers between two values
At 08:52 14/06/2013 -0700, Pedro Noname wrote: Brian Barker wrote Are you sure? When I try this, COUNTIF() looks only at the first cell of your range (e.g. L1:L2), so would simply count values above 20 - wrongly including those above 60. You are correct. I apologize. Oh, thanks - but absolutely no need! Other options are the COUNTIFS function or SUMPRODUCT. Yes: I thought of SUMPRODUCT() too, but didn't see how to use it conveniently. Brian Barker -- To unsubscribe e-mail to: users+unsubscr...@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
[libreoffice-users] Re: Searching numbers between two values
I found a solution! On can see it in the following uploaded file.
BetweenTwoNumbers.ods
http://nabble.documentfoundation.org/file/n4061578/BetweenTwoNumbers.ods
The function is:
=SUMIF('Num 1'.A1;20;'Num 1'.A1)-SUMIF('Num 1'.A1;60;'Num
1'.A1)+SUMIF('Num 2'.A1;20;'Num 2'.A1)-SUMIF('Num 2'.A1;60;'Num
2'.A1)+SUMIF('Num 3'.A1;20;'Num 3'.A1)-SUMIF('Num 3'.A1;60;'Num 3'.A1)
Is there a simpler way to do it?
-
Best Regards from
Pál
--
View this message in context:
http://nabble.documentfoundation.org/Searching-numbers-between-two-values-tp4061491p4061578.html
Sent from the Users mailing list archive at Nabble.com.
--
To unsubscribe e-mail to: users+unsubscr...@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] Re: Searching numbers between two values
At 21:58 14/06/2013 -0700, Csányi Pál wrote:
I found a solution!
You found it? Good! Did you find it in my
message some twelve hours before? ;^)
The function is:
=SUMIF('Num 1'.A1;20;'Num 1'.A1)-SUMIF('Num
1'.A1;60;'Num 1'.A1)+SUMIF('Num
2'.A1;20;'Num 2'.A1)-SUMIF('Num
2'.A1;60;'Num 2'.A1)+SUMIF('Num
3'.A1;20;'Num 3'.A1)-SUMIF('Num 3'.A1;60;'Num 3'.A1)
You seemed to want to exclude 60 (as well as 20)
from your range to be summed. If so, you need =60 (three times), not 60.
Is there a simpler way to do it?
Yes, there is. If the third parameter of SUMIF()
- SumRange - is missing, the values found in
the first parameter - Range - are summed
instead. Since your third parameters are all the
same as your first parameters, you can omit
them. Oh, look: that's what I did in my earlier suggestion!
Brian Barker
--
To unsubscribe e-mail to: users+unsubscr...@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
