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
```