Need to count the amount of times -1 appears above a certain cell

Question

I have the following Excel Spreadsheet -

Hy Newcastle 042216     -1                          Not Matched  1000
Hy Newcastle 042216     6461735 Khanyile;Florence   Success      1000
Hy Newcastle 042216     -1                          Not Matched  1000
Hy Newcastle 042216     6765475 Manana;Witness      Success      1000
Hy Newcastle 042216     -1                          Not Matched  1000
Hy Newcastle 042216     -1                          Not Matched  1000
Hy Newcastle 042216     -1                          Not Matched  1000
Hy Newcastle 042216     6765459 Kubheka;Nomusa      Success      1000
Hy Newcastle 042216     -1                          Not Matched  1000
Hy Newcastle 042216     1905465 Nkosi;Xolani        Success      1000

What I need to do is a count of how many times -1 appears above each person. I've tried a pivot, but then it groups the same employees together and I need it to do the count individually. So for

Kubheka;Nomusa it will be 3,
Khanyile;Florence it will be 1, etc.

Is this possible?

Answer 1

Using VBA, inser a module on the worknook and paste this on the right side:

Public Function countNegatives(name As Range)
    countNegatives = 0
    If name <> "-1" Then
        therow = name.Row
        thecolumn = name.Column
    End If
    endrow = False
    counter = 0
    While endrow = False
        If therow > 1 Then
            therow = therow - 1
            If Cells(therow, thecolumn) = -1 Then
                counter = counter + 1
            Else
                endrow = True
            End If
        Else
            endrow = True
        End If
    Wend
    countNegatives = counter
End Function

On the worksheet, on cell G2 put =countNegatives(D2) and you will have the result.

Answer 2

This VBa script does it

Option Explicit

Sub walkThePlank()

Dim row As Integer
row = 1

Dim total As Integer
total = 0
Do While (Range("D" & row).Value <> "")

    Dim val As String
    val = Range("D" & row).Value

    If (val = "-1") Then
        total = total + 1
    Else
        Range("G" & row).Value = total
        total = 0
    End If


row = row + 1
Loop

End Sub

Before it is run

And after

How do I add VBA in MS Office?

Answer 3

A solution without VBA:

1. Create a helper column with this formula: =IF(C2=-1,"",COUNTIF($C1:C$2,-1)-SUM($G1:G$2))
2. Add pivot table with names and the helper column (or you may even filter your original range to exclude rows without names.