I think I found it.
In A1:B1 enter your values. It doesn't matter if not all rows are
filled.
Enter the following in the following cells:
C1: A>B
C2: A=B
C3: AB1:B1)*(ISNUMBER(A1:A1))*(ISNUMBER(B1:B1)))
D2: =SUM((A1:A1=B1:B1)*(ISNUMBER(A1:A1))*(ISNUMBER(B1:B1)))
D3: =SUM((A1:A1<B1:B1)*(ISNUMBER(A1:A1))*(ISNUMBER(B1:B1)))
If one or more cells in one row are blank, that row doesn't count. For
instance:
A47=39
B47=""
Row 47 doesn't count at all.
Have a nice day!
2015-11-07 18:44 GMT+01:00 Johnny Rosenberg <gurus.knu...@gmail.com>:
> And again…
> -- Forwarded message --
> From: Johnny Rosenberg <gurus.knu...@gmail.com>
> Date: 2015-11-07 18:39 GMT+01:00
> Subject: Re: [libreoffice-users] Need a count formula for calc
> To: Robert Peirce <b...@peirce-family.com>
>
>
> The following works for 1 rows or less.
> {=SUM(IF(A1:A1>B1:B1;1;0))}
> {=SUM(IF(A1:A1=B1:B1;1;0))-SUM(IF(A1:A1="";1;0))}
> {=SUM(IF(A1:A1<B1:B1;1;0))}
> The exception is if there are rows where the A value is empty and the B
> value is not.
>
> For some reason, this didn't work:
>
> {=SUM(IF(A1:A1=B1:B1;1;0))-SUM(IF(OR(A1:A1="";B1:B1="");1;0))}
>
> I'll see if I can improve this a bit more. Not good enough yet, I think…
>
>
> Johnny Rosenberg
>
> 2015-11-07 18:19 GMT+01:00 Johnny Rosenberg <gurus.knu...@gmail.com>:
>
>> 2015-11-07 17:44 GMT+01:00 Robert Peirce <b...@peirce-family.com>:
>>
>>> I have two columns of data, A and B. Sometimes A.n>B.n, A.n>> A.n=B.n. I want to count these occurrences. The closest I could find in
>>> help was countif(), but that doesn't really do what I need. Any ideas?
>>>
>>
>> Yes, a few ideas. I think the best one is using array formulas.
>> If n is known, this will do the trick (assuming n=1000 in this case):
>> {=SUM(IF(A1:A1000>B1:B1000;1;0))}
>> {=SUM(IF(A1:A1000=B1:B1000;1;0))}
>> {=SUM(IF(A1:A1000<B1:B1000;1;0))}
>>
>> To enter an array formula, enter the formula as usual but instead of
>> hitting Enter when done, hit Ctrl+Shift+Enter.
>> The {} thing is added automatically, you should not enter them manually.
>>
>> The downside with my example here is when there are empty cells in
>> A1:B1000. That means that if n is not known, you can't just select a high
>> number, such as A1:A100, because all empty rows will be considered A=B…
>>
>> I've only been playing with this for a couple of minutes yet so I am not
>> sure how to solve that, but I'm pretty sure it's possible.
>>
>> I'll write back if I find a solution to that before anyone else does, but
>> I'm not sure how much time I will spend on this…
>>
>>
>> Johnny Rosenberg
>>
>>
>>>
>>> --
>>> 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
>>>
>>
>>
>
>
--
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
