Searching numbers between two values

users
#1

Hi,

I'm using LibreOffice 4.0.3.3 (Build az.: 400m0(Build:3)).

BetweenTwoNumbers.ods
<http://nabble.documentfoundation.org/file/n4061491/BetweenTwoNumbers.ods>

In the uploaded file BetweenTwoNumbers.ods there are four sheets:

between 20-60, Num 1, Num 2, Num 3.

On the 'between 20-60' sheet there are three functions to determine how many
numbers can find on sheets Num 1, Num 2 and Num 3 respectivly ( one by one )
and there is a fourth function that sholud count that at once on all Num 1-3
sheets.

As one can see, none of these three functions works:
=COUNTIF('Num 1'.A1;AND('Num 1'.A1>20;'Num 1'.A1<60))
=COUNTIF('Num 2'.A3;AND('Num 2'.A3>20;'Num 2'.A3<60))
=COUNTIF('Num 3'.A5;AND('Num 3'.A5>20;'Num 3'.A5<60))

moreover, I don't know how to create fourth function.

Can one advice me how to go further?

#2

Hi

This is a possible solution

=COUNTIF('Num 1'.A1;L1:L2) where L1 = <20 and L2 = >60

=COUNTIF('Num 2'.A1;L3:L4) where L3 = <20 and L4 = >60

=COUNTIF('Num 3'.A1;L5:L6) where L5 = <20 and L6 = >60

I believe the fourth function is =SUM(J1:J5) ?

#3

I'm using LibreOffice 4.0.3.3 (Build az.: 400m0(Build:3)).

BetweenTwoNumbers.ods <http://nabble.documentfoundation.org/file/n4061491/BetweenTwoNumbers.ods>
In the uploaded file BetweenTwoNumbers.ods there are four sheets: between 20-60, Num 1, Num 2, Num 3. On the 'between 20-60' sheet there are three functions to determine how many numbers can find on sheets Num 1, Num 2 and Num 3 respectively ( one by one ) and there is a fourth function that should count that at once on all Num 1-3 sheets.

As one can see, none of these three functions works:
=COUNTIF('Num 1'.A1;AND('Num 1'.A1>20;'Num 1'.A1<60))
=COUNTIF('Num 2'.A3;AND('Num 2'.A3>20;'Num 2'.A3<60))
=COUNTIF('Num 3'.A5;AND('Num 3'.A5>20;'Num 3'.A5<60))

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.

Brian Barker

#4

(You have the inequalities backwards, I think - but no matter.)

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.

Brian Barker

#5

Yes, this helps. Thank you!

#6

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.

#7

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.

#8

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.
Other options are the COUNTIFS function or SUMPRODUCT.

#9

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

#10

Try:
=SUMIF('Num 1'.A1:Z99;">20")-SUMIF('Num 1'.A1:Z99;">=60")+SUMIF('Num 2'.A1:Z99;">20")-SUMIF('Num 2'.A1:Z99;">=60")+SUMIF('Num 3'.A1:Z99;">20")-SUMIF('Num 3'.A1:Z99;">=60")

There may be neater solutions!

I trust this helps.

Brian Barker

#11

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?

#12

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

#13

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.

#14

Hi Miguel Ángel,

thank you for advices.

#15

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.