how to compare 2 cells in range
 

Hi, sorry for my English :)

My data:

plan | done
100 | 0
100 | 100
200 | 150
100 | 100
..... .... hundreds equal cells

Is any formula, which gives me result=2 ?

I don't want (i can't) to add new column with, for example, =a2=b2 and then
count false.

I want only count of false.

I hope my post is clear :)


tomekgomek

how to compare 2 cells in range
 

=sumproduct(--(A1:A100<B1:B100))
will count the number of non equal pairs in rowns 1-100 in columns a and B
note you cannot use A:A<B:B in sumproduct
the--( chnages the logical true false to a numeric 1 0

"tomek gomek" wrote:

Hi, sorry for my English :)

My data:

plan | done
100 | 0
100 | 100
200 | 150
100 | 100
..... .... hundreds equal cells

Is any formula, which gives me result=2 ?

I don't want (i can't) to add new column with, for example, =a2=b2 and then
count false.

I want only count of false.

I hope my post is clear :)


tomekgomek

how to compare 2 cells in range
 

With your data in column A and B, with a macro:-

Sub strata()
Dim i As Integer
i = Range("A1").CurrentRegion.Rows.Count
Set myrange = Range("A1:A" & i)
For Each c In myrange
If c.Value < c.Offset(0, 1).Value Then Count = Count + 1
Next
MsgBox (Count & " difference between column A & B")
Cells(1, 3).Value = Count
End Sub

Right click sheet tab view code and paste in

Mike

"tomek gomek" wrote:

Hi, sorry for my English :)

My data:

plan | done
100 | 0
100 | 100
200 | 150
100 | 100
..... .... hundreds equal cells

Is any formula, which gives me result=2 ?

I don't want (i can't) to add new column with, for example, =a2=b2 and then
count false.

I want only count of false.

I hope my post is clear :)


tomekgomek

how to compare 2 cells in range
 

Użytkownik "Mike H" napisał w wiadomości
...
With your data in column A and B, with a macro:-

Sub strata()
Dim i As Integer
i = Range("A1").CurrentRegion.Rows.Count
Set myrange = Range("A1:A" & i)
For Each c In myrange
If c.Value < c.Offset(0, 1).Value Then Count = Count + 1
Next
MsgBox (Count & " difference between column A & B")
Cells(1, 3).Value = Count
End Sub

Right click sheet tab view code and paste in

Mike

thanks, but VBA isn't for my boss :) anyway, thanks

tomekgomek

how to compare 2 cells in range
 

Użytkownik "bj" napisał w wiadomości
...
=sumproduct(--(A1:A100<B1:B100))
will count the number of non equal pairs in rowns 1-100 in columns a and B
note you cannot use A:A<B:B in sumproduct
the--( chnages the logical true false to a numeric 1 0


great, super, wow :)
thanks

tomek