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?

You can do it either with a helper column (changing it's value when name column changes from a name to -1) and a pivot, or you can use array formulas which do the same without extra column but the formula is much more complex, which one would you prefer? — Máté Juhász, Jun 30 '15 at 07:36

score 0 · Answer 1 · answered Jun 30 '15 at 08:10

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.

score 0 · Accepted Answer · edited Mar 20 '17 at 10:16

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

enter image description here

And after

enter image description here

How do I add VBA in MS Office?

score 0 · Answer 3 · answered Jul 08 '15 at 11:53

0

A solution without VBA:

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

enter image description here

answered Jul 08 '15 at 11:53

Máté Juhász

21,403
6
54
73

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

3 Answers3