[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