Procura de intervalo do Excel COUNTIF retornando # N / A

Question

Estou usando o Excel para pesquisar vários dias (meus intervalos) em uma planilha de horas por horas trabalhadas (meu HLOOKUP lê o valor abaixo do número do departamento) por vários membros da equipe para diferentes departamentos.

Eu criei a seguinte fórmula que funciona para a maioria da minha planilha, exceto se o valor da célula de pesquisa (AB9) não for encontrado no primeiro intervalo ($ B $ 9: $ E $ 10). Para outros, funciona (se o valor não for encontrado nos intervalos posteriores, ele não retornará um # N / A), somente se o valor não for encontrado no primeiro intervalo.

=SUM(IF(COUNTIF($B$9:$E$10,AB9),HLOOKUP(AB9,$B$9:$E$10,2,FALSE),""))+(IF(COUNTIF($F$9:$I$10,AB9),HLOOKUP(AB9,$F$9:$I$10,2,FALSE),""))+(IF(COUNTIF($J$9:$M$10,AB9),HLOOKUP(AB9,$J$9:$M$10,2,FALSE),""))+(IF(COUNTIF($N$9:$Q$10,AB9),HLOOKUP(AB9,$N$9:$Q$10,2,FALSE),""))+(IF(COUNTIF($R$9:$U$10,AB9),HLOOKUP(AB9,$R$9:$U$10,2,FALSE),""))+(IF(COUNTIF($V$9:$Y$10,AB9),HLOOKUP(AB9,$V$9:$Y$10,2,FALSE),""))

Alguém pode me explicar por que isso está falhando e se eu não deveria estar usando o COUNTIF para isso, o que mais eu deveria estar usando e por quê?

Idealmente, eu teria minha fórmula procurando por números únicos (departamentos) nos intervalos, relatando-os e, em seguida, listando abaixo as horas trabalhadas para cada um - mas isso é um pouco acima da minha especialidade (atualmente)! ;)

    

Answer 1

Um colega e eu criamos uma solução melhor do que aninhar todos os IFs juntos.

Criamos =SUMIF($B7:$AI7,AB9,$B8:$AI8) , o que funciona muito melhor, já que não há necessidade de vários IF aninhados.

Eu o expandi ainda mais, permitindo um departamento padrão e incluindo o tempo S (Doente) e H (Feriado), que é adicionado ao total de horas do departamento padrão.

=SUMIF($B7:$AI7,AL7,$B8:$AI8)+IF($AJ7=AL7,SUMIF($B7:$AI7,"S",$B8:$AI8)+SUMIF($B7:$AI7,"H",$B7:$AI7),0)

Estou indo além, pois incluirá as taxas horárias e as horas extras a seguir. Me deseje sorte!

    

Answer 2

As fórmulas do Excel tratam os valores de erro especiais de # N / A e # DIV / 0 de maneira diferente de outros valores (reais). Dependendo das fórmulas usadas, elas são retornadas como resultado, não importa onde elas sejam exibidas. Portanto, o seu HLOOKUP está retornando # N / A quando não pode encontrar o valor, e mesmo que você esteja tentando ignorar a avaliação do HLOOKUP na instrução IF e retorne apenas "", o Excel ainda está avaliando-o e retornando # N / A assim que é avaliado.

Além disso, como você está trabalhando com valores e não com texto, seu valor de retorno deve ser 0 e não "".

Em vez da declaração IF externa em cada SUM, você deve usar IFNA da seguinte forma:

IFNA(HLOOKUP(AB9,$B$9:$E$10,2,FALSE),0)

A fórmula IFNA retorna o valor no primeiro parâmetro se ele realmente funciona e produz um valor, e retorna um "" se não funcionar.

O link fornecido tem uma planilha de amostra para mostrar como ele funciona com um VLOOKUP, para que você possa testar a fórmula. Baseado na amostra lá, este é o caso exato que você gostaria de usar IFNA.

Então, se eu estou lendo o que você está tentando fazer corretamente, a fórmula inteira deve ser:

=SUM(IFNA(HLOOKUP(AB9,$B$9:$E$10,2,FALSE),0),IFNA(HLOOKUP(AB9,$F$9:$I$10,2,FALSE),""),IFNA(HLOOKUP(AB9,$J$9:$M$10,2,FALSE),""),IFNA(HLOOKUP(AB9,$N$9:$Q$10,2,FALSE),""),IFNA(HLOOKUP(AB9,$R$9:$U$10,2,FALSE),""),IFNA(HLOOKUP(AB9,$V$9:$Y$10,