Precisa contar a quantidade de vezes que -1 aparece acima de uma determinada célula

Question

Eu tenho a seguinte planilha do Excel -

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

O que eu preciso fazer é uma contagem de quantas vezes -1 aparece acima de cada pessoa. Eu tentei um pivô, mas ele agrupa os mesmos funcionários e eu preciso fazer a contagem individualmente. Então, para

Kubheka; Nomusa será 3,
Khanyile; Florença será 1, etc.

Isso é possível?

    

Answer 1

Este script VBa faz isso

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

Antes de ser executado

Edepois

Como eu adiciono o VBA no MS Office?

    

Answer 2

Usando o VBA, insira um módulo na worknook e cole-o no lado direito:

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

Na planilha, na célula G2 coloque =countNegatives(D2) e você terá o resultado.

    

Answer 3

Uma solução sem VBA:

1. Crie uma coluna auxiliar com esta fórmula: =IF(C2=-1,"",COUNTIF($C1:C$2,-1)-SUM($G1:G$2))
2. Adicione uma tabela dinâmica com nomes e a coluna auxiliar (ou você pode até filtrar seu intervalo original para excluir linhas sem nomes.