CountIf 2 Variables

Posted by Michael on May 15, 2001 4:22 PM

I have 2 columns and want to count the number of conflicts given 2 variables
A B
1 1
1 0
0 1

I want to know how many times in column B that there is a "1" and a corresponding "1" in column A

Posted by Mark W. on May 15, 2001 5:00 PM

{=SUM((A2:A4*B2:B4)+0)}

Posted by Mark W. on May 15, 2001 5:03 PM

Revision...

Sorry, you don't need the +0... use:

{=SUM(A2:A4*B2:B4)}

I noticed that you asked a similiar question
earlier. Is this starting to make sense?

Posted by Kevin James on May 15, 2001 5:46 PM

Michael:

Mark's formula will work if you are indeed dealing only with 1's and zeros. If you dealing with zero and any other number you will need:

=SUM((A2:A4*B2:B4)+0)/(A2*A2)

If zero doesn't even play a role in the real situation, the way around that is in a third column use something like this:

(Starting in cell C2):
=if(A2=B2,1,"")

copy that cell down corresponding to the last row for columns A & B.
At the end of column C, write a sum to count all the matches.
There is still one problem with this suggestion and that is that it doesn't take into consideration which column had the undesirable value.

In that case, yet another formula will be needed.

Kevin

Posted by Michael on May 15, 2001 7:28 PM

Posted by Mark W. on May 16, 2001 6:43 AM

Kevin, you lost me. What does "/(A2*A2)" accomplish?
If I was dealing with zero and non-zero values I'd
use...

{=SUM(IF(A2:A4,1,)*B2:B4)}