How to sum cells that don't contain a specific value

Question

I am trying count rows in Excel from the first sheet if they are different from the rows on the second sheet.

But there is something wrong.

inicijaliDT is a range with data I check on Sheet2.

inicijali = wsNOSTROSheet.Range("R" & brojac).Value
For Each Row In inicijaliDT
    If inicijali <> Row.Value Then
        brojRedaka = brojRedaka + 1
    'Else
        'Exit For
    End If
    Exit For
Next Row

Not very clear what you need accomplishing... Are there the same number of rows in each sheet? Do you want comparing rows 1 of the first sheet with row 1 of the second and so on? And count the rows which are not identic? — FaneDuru, Jan 13 '21 at 10:12
(2) Is the range `inicijaliDt` a single column range? (3) You should think about the names you give your variables (e.g. `Row` is not a good name for a cell). — Super Symmetry, Jan 13 '21 at 10:14

score 1 · Answer 1 · answered Jan 13 '21 at 10:24

If I correctly understood which you want accomplishing, please try the next code:

Sub testCompareRows()
 Dim sh1 As Worksheet, sh2 As Worksheet, lastR As Long, i As Long, cnt As Long
 
 Set sh1 = ActiveSheet 'use here your first sheet
 Set sh2 = sh1.Next    'use here your second sheet
 lastR = sh1.UsedRange.rows.count + sh1.UsedRange.row - 1
 For i = 1 To lastR
    With Application
        If Join(.Transpose(.Transpose(sh1.rows(i)))) <> _
            Join(.Transpose(.Transpose(sh2.rows(i)))) Then cnt = cnt + 1
    End With
 Next i
 If cnt > 0 Then MsgBox "There are " & cnt & " different rows..."
End Sub

You did not ask the clarification questions and the code assumes that both sheets have the same number of rows, each row number in a first sheet to be compared with the same row number of the second one...

VBasic2008 · Answer 2 · 2021-01-13T12:19:59.050

Count Not Equal

If inicijaliDT is a one-column range, you can do one of the following:

Sub testLoop()
    Dim cel As Range
    Dim brojRedaka As Long
    inicijali = wsNOSTROSheet.Range("R" & brojac).Value
    For Each cel In inicijaliDT.Cells
        If cel.Value <> inicijali Then
            brojRedaka = brojRedaka + 1
        End If
    Next cel
    'Debug.Print brojRedaka
End Sub

Sub testCountIf()
    Dim brojRedaka As Long
    inicijali = wsNOSTROSheet.Range("R" & brojac).Value
    brojRedaka = inicijaliDT.Rows.Count _
        - Application.CountIf(inicijaliDT, inicijali)
    'Debug.Print brojRedaka
End Sub

If inicijaliDT is a multi-column range and you are trying to count the number of rows where inicijali is not found in any of their cells, you can do one of the following:

Sub testRowLoop()
    Dim RowRange As Range
    Dim cel As Range
    Dim brojRedaka As Long
    Dim isFound As Boolean
    inicijali = wsNOSTROSheet.Range("R" & brojac).Value
    For Each RowRange In inicijaliDT.Rows
        For Each cel In RowRange.Cells
            If cel.Value = inicijali Then
                isFound = True
                Exit For
            End If
        Next cel
        If isFound Then
            isFound = False
        Else
            brojRedaka = brojRedaka + 1
        End If
    Next RowRange
    'Debug.Print brojRedaka
End Sub

Sub testRowMatch()
    Dim RowRange As Range
    Dim brojRedaka As Long
    inicijali = wsNOSTROSheet.Range("R" & brojac).Value
    For Each RowRange In inicijaliDT.Rows
        If IsError(Application.Match(inicijali, RowRange, 0)) Then
            brojRedaka = brojRedaka + 1
        End If
    Next RowRange
    ' Debug.Print brojRedaka
End Sub

Gass · Answer 3 · 2021-01-13T11:31:26.350

This one you can customize. Remember to config the values in the code before you run it.

'TASK: count rows in first sheet that 
'have a different value to the same cells in the second sheet.

Sub countRows()

Dim positioner As Long, count As Long, last_row As Long
Dim db_first_row As Byte, col_n As Byte, name_s1 As String
Dim name_s2 As String, display_ans As String, msg As String
Dim display_in_cell As Boolean, display_in_msgbox As Boolean

'CONFIR HERE BEFORE YOU RUN IT
'------------------------------------------------
name_s1 = "Sheet1"         'name of first sheet
name_s2 = "Sheet2"         'name of second sheet
db_first_row = 2           'first row of the data set
col_n = 2                  'colum number of the data set
count = 0                  'from what number would you like to start counting?
display_in_cell = True     'do you want to display the answer in a specific cell?
display_ans = "C3"         'in what cell?
display_in_msgbox = True   'do you want to display the answer in a msgbox?
msg = "Different values counted: " 'message to display in msgbox
'------------------------------------------------

'get the last row of the data set
last_row = Sheets(name_s1).Cells(Rows.count, col_n).End(xlUp).Row

For positioner = db_first_row To last_row
    
    'if values of both sheets match in the same cell then count
    If Sheets(name_s1).Cells(positioner, col_n) <> _
    Sheets(name_s2).Cells(positioner, col_n) Then
            count = count + 1
        End If

Next positioner

If display_in_cell Then
    Sheets(name_s1).Range(display_ans) = count
End If

If display_in_msgbox Then
    MsgBox msg & count
End If

End Sub

A few gentle comments: 1- You should always declare the type of each variable in a `Dim` statement, otherwise, all your variables will be of type `Variant` except the last on each line. 2- Whenever you're tempted to use `Integer` or `Byte` you should use `Long`. 3- In your `If` conditions you do not need the `= True` part if the variable is of type `Boolean` — Super Symmetry, Jan 13 '21 at 11:07
Thanks for the suggestions. They are very helpful. Just one question, why would I use a Long variable for a number probably not bigger than 10, as the db_first_row? — Gass, Jan 13 '21 at 11:11
Take a look at [this answer](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long/26409520#26409520) to *Why Use Integer Instead of Long?*. — VBasic2008, Jan 13 '21 at 11:22

How to sum cells that don't contain a specific value

3 Answers3

Count Not Equal