MS Excel: como usar o COUNTIF () dinamicamente

Question

Eu tenho uma planilha para manter um registro de itens defeituosos. Cada item é registrado em uma linha separada e há um "Usável"? coluna para cada item. Isto é o que e a linha de exemplo se parece:

  Item   | Usable?
 ---------------
1 Item1  | Yes
2 Item2  | No
3 Item3  | Unknown
4 Item4  | No

O "utilizável?" coluna tem um menu suspenso para cada célula para definir seu status como "Sim", "Não" ou "Desconhecido" e eu uso a função COUNTIF () para contar o número total de cada status. Eu quero ter a contagem na última linha, imediatamente após o último item registrado, ou seja:

  Item   | Usable?
 ---------------
1 Item1  | Yes
2 Item2  | No
3 Item3  | Unknown
4 Item4  | No
 ---------------
5 Total Yes: | 1
6 Total No:  | 2
7 Total Unknown: | 1

O problema é que COUNTIF () recebe um conjunto predefinido de células e quando eu adiciono uma nova linha, COUNTIF () não leva a nova célula para a contagem a menos que eu modifique a função.

Existe uma maneira de fazer o COUNTIF () incluir os novos dados sem que eu modifique a função?

    

Answer 1

Você pode usar: =COUNTIF($B$2:INDIRECT("$B"&ROW()-1),INDIRECT("$A"&ROW()))
Escreva o status na última linha (a que você deseja contar e você pode alterá-lo sem alterar a fórmula)
na segunda coluna ao lado do status da mesma linha, escreva a fórmula acima.
Coluna B é o status (Sim, Não, Desconhecido)
Coluna A é os itens

Caso você queira escrever:

Total Yes       2
Total No        2
Total Unknown   1  

Diretamente abaixo, você pode usar:
=COUNTIF($B$2:INDIRECT("$B"&ROW()-1),"Yes") ao lado de Sim =COUNTIF($B$2:INDIRECT("$B"&ROW()-2),"No") ao lado de No
=COUNTIF($B$2:INDIRECT("$B"&ROW()-3),"Unknown") ao lado de Desconhecido

Qualquer que seja a linha que você inserir, será contada mesmo que você selecione Total Sim e insira uma Linha

    

Answer 2

Opção 1: coluna inteira de referência

Como desvantagem, você teria que colocar seus totais em uma coluna ou folha diferente.

=COUNTIF(B:B,"Yes")

Opção 2: linha em branco acima dos totais

Inserir uma linha dentro de um intervalo irá expandi-la. Ao incluir a linha em branco na sua fórmula, o Excel adicionará automaticamente o novo intervalo aos seus totais.

Apenas insira a linha em branco e não a linha de totais.

    A       B
 ---------------
1 Item1  | Yes
2 Item2  | No
3 Item3  | Yes
4
5 TotYes | =COUNTIF(B1:B4,"Yes")

Clique com o botão direito no cabeçalho da linha 4 e clique em "Inserir". Você notará que a fórmula COUNTIF se tornará B1:B5 .