2015 Archives by date, by thread · List index

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

```I think I found it.

In A1:B10000 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: A<B

In the following cells, hit Ctrl+Shift+Enter instead of just Enter to
finish the formulas:
D1: =SUM((A1:A10000>B1:B10000)*(ISNUMBER(A1:A10000))*(ISNUMBER(B1:B10000)))
D2: =SUM((A1:A10000=B1:B10000)*(ISNUMBER(A1:A10000))*(ISNUMBER(B1:B10000)))
D3: =SUM((A1:A10000<B1:B10000)*(ISNUMBER(A1:A10000))*(ISNUMBER(B1:B10000)))

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.knugum@gmail.com>:

```
```And again…
---------- Forwarded message ----------
From: Johnny Rosenberg <gurus.knugum@gmail.com>
Date: 2015-11-07 18:39 GMT+01:00
Subject: Re: [libreoffice-users] Need a count formula for calc
To: Robert Peirce <bob@peirce-family.com>

The following works for 10000 rows or less.
{=SUM(IF(A1:A10000>B1:B10000;1;0))}
{=SUM(IF(A1:A10000=B1:B10000;1;0))-SUM(IF(A1:A10000="";1;0))}
{=SUM(IF(A1:A10000<B1:B10000;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:A10000=B1:B10000;1;0))-SUM(IF(OR(A1:A10000="";B1:B10000="");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.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.