Countif using a range comparison

[smurphy1977]

I am trying to come up with a Countif formual using a range comparison but I don't know if it is possible.

What I a trying to do is to count how many cells differences there are . For example if A1 does not equal B1 then count, if A2 does not equal B2 then count.

A B
1 1
1 2
2 1
2 2

If I had the correct formula in this example I should generate a count of 2.

I just can't get it to work. Can anyone help? Is it even possible?

I was thinking =COUNTIF(A1:A4, "<>B1:B4") but that doesn't work. Doing it that way it counts everthing and gives me a 4

-Sage

 

[MrKowz]

Try the following:

Excel Workbook
	A	B	C	D
1	1	1		2
2	1	2
3	2	1
4	2	2
Sheet1

Excel 2003

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[Aladin Akyurek]

I am trying to come up with a Countif formual using a range comparison but I don't know if it is possible.

What I a trying to do is to count how many cells differences there are . For example if A1 does not equal B1 then count, if A2 does not equal B2 then count.

A B
1 1
1 2
2 1
2 2

If I had the correct formula in this example I should generate a count of 2.

I just can't get it to work. Can anyone help? Is it even possible?

I was thinking =COUNTIF(A1:A4, "<>B1:B4") but that doesn't work. Doing it that way it counts everthing and gives me a 4

-Sage

Try...

=SUMPRODUCT((A1:A4<>B1:B4)+0)

 

[smurphy1977]

Awesome! Would you be able to talk me through the logic because I am trying to wrap my head around it.

I've got = Count (if range 1 does not equal range 2, then ......)

and then I get lost.

And say I wanted to excude any 3's that were in column b in the count what would I add then.
Would it be:

=COUNT(IF(A1:A4<>B1:B4,A1:A4,""))-Count(if(B1:B4=3,1,0) ????

Try the following:

Excel Workbook
	A	B	C	D
1	1	1		2
2	1	2
3	2	1
4	2	2
Sheet1

Excel 2003

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself

 

[delaneyjm]

Bit late as usual...

=SUMPRODUCT(--(A2:A6<>B2:B6))

 

[smurphy1977]

I like this one too! Forgive me if this is a dumb question but what does the "--" mean in the formula?

Bit late as usual...

=SUMPRODUCT(--(A2:A6<>B2:B6))

 

[delaneyjm]

it converts the true's and false's returned by the A2:A6<>B2:B6 into 1's and 0's. 1 being true and 0 being false.