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 <[email protected]>: > And again… > ---------- Forwarded message ---------- > From: Johnny Rosenberg <[email protected]> > Date: 2015-11-07 18:39 GMT+01:00 > Subject: Re: [libreoffice-users] Need a count formula for calc > To: Robert Peirce <[email protected]> > > > 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 <[email protected]>: > >> 2015-11-07 17:44 GMT+01:00 Robert Peirce <[email protected]>: >> >>> 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: [email protected] >>> 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: [email protected] 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
