Como criar uma fórmula que possa contar valores em várias colunas em linhas especificadas

Question

Eu tenho uma planilha do Excel onde tenho várias tarefas e vários produtos. Cada tarefa para cada produto é classificada em vermelho. âmbar ou verde, dependendo do progresso até o momento. Cada tarefa também tem um dono.

Eu quero criar outra tabela que mostre quantas classificações de vermelho, âmbar e verde são atribuídas a cada proprietário. Eu posso fazer isso muito facilmente com uma função countif, mas o problema adicional é que novas tarefas e proprietários estão sendo adicionados ao final da minha lista por outros no meu departamento. Assim, a única maneira de manter minha mesa atualizada é reordenar constantemente a tabela pelo proprietário e ajustar os intervalos no countif.

Aqui está um link para um exemplo que fiz:

Encontrei algumas funções de índice que podem retornar vários valores e contar os valores, mas não sou inteligente o suficiente para fazê-los funcionar em várias colunas.

Obrigado,

    

Answer 1

Para o layout atual, você pode usar isso na célula J4:

=SUMPRODUCT((OFFSET($D$3,0,0,COUNTA($C:$C)-1,COUNTA($2:$2)-2)=J$3)*(OFFSET($C$3,0,0,COUNTA($C:$C)-1)=$I4))

COUNTA($C:$C)-1 retorna a altura da tabela de dados (o -1 deve desconsiderar a linha de cabeçalho). Isso depende da coluna C que contém os proprietários e não deve haver dados após o último proprietário ou conter espaços em branco entre as linhas nos dados em si.

COUNTA($2:$2)-2 da mesma forma retorna a amplitude da tabela de dados (e, desta vez, eu tenho -2 porque há duas colunas antes de o conjunto de dados ser iniciado). Novamente, não deve haver falta de cabeçalhos na tabela.

(OFFSET($D$3,0,0,COUNTA($C:$C)-1,COUNTA($2:$2)-2) retorna o intervalo começando em D3 e com altura e largura calculadas acima.

OFFSET($C$3,0,0,COUNTA($C:$C)-1) retorna o intervalo que contém os proprietários.

(OFFSET($D$3,0,0,COUNTA($C:$C)-1,COUNTA($2:$2)-2)=J$3) compara os dados com a célula J3. Na minha tabela, coloquei r em J3 para que eu pudesse arrastar a fórmula pela tabela apenas duas vezes sem ter que alterar nada na fórmula, mas você pode substituir o J$3 por "r" , você terá para ajustá-lo para cada coluna uma vez. A expressão retornará TRUE para correspondências e FALSE caso contrário.

(OFFSET($C$3,0,0,COUNTA($C:$C)-1)=$I4) compara os proprietários com o proprietário na célula I4. Retorna TRUE para correspondências e FALSE caso contrário.

Quando os dois resultados acima são multiplicados, você acaba com uma série de 1 e 0 , que SUMPRODUCT adiciona para dar uma contagem.