Need a count formula for calc

users
#1

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?

#2

Aaaaah!!!! Did it again…!

#3

And again…

#4

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!

#5

X1: =SIGN(A1-B1)
copy down

=COUNTIF(X1:X999;0)
=COUNTIF(X1:X999;1)
=COUNTIF(X1:X999;-1)

or use FREQUENCY with column X
or create a pivot table from column X

#6

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.

#7

I think for not take care of the number of blank cells, it's easier and
versatile with SUMPRODUCT()

=SUMPRODUCT($A$2:$A$12>$B$2:$B$12;$A$2:$A$12>0;$B$2:$B$12>0)

Attached a sample file.

Miguel Ángel.

#8

I think for not take care of the number of blank cells, it's easier and
versatile with SUMPRODUCT()

=SUMPRODUCT($A$2:$A$12>$B$2:$B$12;$A$2:$A$12>0;$B$2:$B$12>0)

What if negative numbers are used?

Attached a sample file.

Attachments doesn't usually make it though the mailing list.