2015 Archives by date, by thread · List index

# [libreoffice-users] Re: Fwd: Need a count formula for calc

I replied directly as well. This solved my problem perfectly. The blank line problem was easy since I know there are always two, so:
```
{=SUM(IF(A1:A1000=B1:B1000;1;0))-2}

solves that problem.

On 11/7/15 12:43 PM, Johnny Rosenberg wrote:
```
```Aaaaah!!!! Did it again…!
---------- Forwarded message ----------
From: Johnny Rosenberg <gurus.knugum@gmail.com>
Date: 2015-11-07 18:42 GMT+01:00
Subject: Re: [libreoffice-users] Need a count formula for calc
To: Robert Peirce <bob@peirce-family.com>

Aaaahhh, I didn't write to this list for quite a while, so I forgot that my
reply goes to the original poster rather than to the list if I don't do
anything about it… sorry for that.

Here's what I replied:

2015-11-07 18:19 GMT+01:00 Johnny Rosenberg <gurus.knugum@gmail.com>:

```
```2015-11-07 17:44 GMT+01:00 Robert Peirce <bob@peirce-family.com>:

```
```I have two columns of data, A and B.  Sometimes A.n>B.n, A.n<B.n or
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:A1000000, 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+unsubscribe@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+unsubscribe@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
```

### Context

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.