A adição de uma condição a uma fórmula SUM resulta em erro # N / A

Question

Estou tentando somar um intervalo com base em algumas condições.

Quando eu entro

=SUM(IF((INDIRECT("CommissionDetail!$Z$2:$Z$"&COUNT(CommissionDetail!$N:$N))<>0)*(INDIRECT("CommissionDetail!$Z$2:$Z$"&COUNT(CommissionDetail!$N:$N))<>""),INDIRECT("CommissionDetail!$AF$2:$AF$"&COUNT(CommissionDetail!$N:$N))/INDIRECT("CommissionDetail!$Z$2:$Z$"&COUNT(CommissionDetail!$N:$N)),0))

a fórmula retorna um resultado válido.

No entanto, quando eu adiciono a condição

LEFT(INDIRECT("CommissionDetail!$AL$2:$AL$"&COUNTA(CommissionDetail!$N:$N)),SEARCH("(",INDIRECT("CommissionDetail!$AL$2:$AL$"&COUNTA(CommissionDetail!$N:$N)))-2)="As and When"

da seguinte forma:

=SUM(IF((LEFT(INDIRECT("CommissionDetail!$AL$2:$AL$"&COUNTA(CommissionDetail!$N:$N)),SEARCH("(",INDIRECT("CommissionDetail!$AL$2:$AL$"&COUNTA(CommissionDetail!$N:$N)))-2)="As and When")*(INDIRECT("CommissionDetail!$Z$2:$Z$"&COUNT(CommissionDetail!$N:$N))<>0)*(INDIRECT("CommissionDetail!$Z$2:$Z$"&COUNT(CommissionDetail!$N:$N))<>""),INDIRECT("CommissionDetail!$AF$2:$AF$"&COUNT(CommissionDetail!$N:$N))/INDIRECT("CommissionDetail!$Z$2:$Z$"&COUNT(CommissionDetail!$N:$N)),0))

então recebo o erro # N / A.

Mesmo quando eu removo a operação de divisão, por exemplo

=SUM(IF((LEFT(INDIRECT("CommissionDetail!$AL$2:$AL$"&COUNTA(CommissionDetail!$N:$N)),SEARCH("(",INDIRECT("CommissionDetail!$AL$2:$AL$"&COUNTA(CommissionDetail!$N:$N)))-2)="As and When")*(INDIRECT("CommissionDetail!$Z$2:$Z$"&COUNT(CommissionDetail!$N:$N))<>0)*(INDIRECT("CommissionDetail!$Z$2:$Z$"&COUNT(CommissionDetail!$N:$N))<>""),INDIRECT("CommissionDetail!$AF$2:$AF$"&COUNT(CommissionDetail!$N:$N)),0))

Ainda obtenho o erro # N / A.

Quando faço uma contagem na coluna Z da seguinte forma:

=COUNT(IF((LEFT(INDIRECT("CommissionDetail!$AL$2:$AL$"&COUNTA(CommissionDetail!$N:$N)),SEARCH("(",INDIRECT("CommissionDetail!$AL$2:$AL$"&COUNTA(CommissionDetail!$N:$N)))-2)="As and When")*(INDIRECT("CommissionDetail!$Z$2:$Z$"&COUNT(CommissionDetail!$N:$N))<>0)*(INDIRECT("CommissionDetail!$Z$2:$Z$"&COUNT(CommissionDetail!$N:$N))<>""),INDIRECT("CommissionDetail!$Z$2:$Z$"&COUNT(CommissionDetail!$N:$N))))

Eu recebo um resultado válido, então sei que há pelo menos algumas células que satisfazem a condição que mencionei acima.

Por que, então, recebo o erro # N / A ao somar?

Estou especificamente interessado em entender por que a fórmula deixa de ser válida depois que uma condição é adicionada e é satisfeita pelo menos mais de uma vez e que funciona na fórmula COUNT.

O objetivo final é determinar a comissão média do tipo "como e quando" cobrada por mês em um produto. Eu removi as condições para dividir a comissão por mês para evitar complicar demais as fórmulas já confundíveis acima. A justificativa para usar essas fórmulas de matriz é que elas são dinâmicas e são calculadas apenas uma vez por mês e precisam ser transferidas para novas pastas de trabalho mensalmente por usuários que não podem adicionar colunas auxiliares ou interpretar o funcionamento das fórmulas. Usar colunas auxiliares me leva ao meu objetivo, mas infelizmente as colunas auxiliares não são opções nessa instância

Alguma idéia?

    

Answer 1

Na primeira fórmula, você usou COUNT, ou seja,

COUNT(CommissionDetail!$N:$N)

mas na parte adicional você usou COUNTA, ou seja,

COUNTA(CommissionDetail!$N:$N)

Se houver algum valor de texto na coluna N (por exemplo, linha de cabeçalho), eles não fornecerão o mesmo número (portanto, erro # N / A), pois a contagem COUNT contará apenas números, COUNTA contará todas as entradas.

Sugiro que mude para o COUNTA, porque usar COUNT ignorará a última linha se tiver uma linha de cabeçalho

Você provavelmente pode simplificar um pouco, não acha que precisa da parte SEARCH na nova condição, porque não procurar células que começam com As e When ( ... ..e você poderia usar IFERROR em vez de verificar espaços em branco ou zeros como divisores, ou seja, incluindo nova condição (e COUNTA em toda conforme sugerido) que seria:

=SUM(IFERROR(IF(LEFT(INDIRECT("CommissionDetail!$AL$2:$AL$"&COUNTA(CommissionDetail!$N:$N)),13)="As and When (",INDIRECT("CommissionDetail!$AF$2:$AF$"&COUNTA(CommissionDetail!$N:$N))/ INDIRECT("CommissionDetail!$Z$2:$Z$"&COUNTA(CommissionDetail!$N:$N))),0))

confirmado com CTRL + SHIFT + ENTER

... e uma melhoria adicional para tornar a fórmula mais legível é definir o seu elemento repetitivo, ou seja,

=COUNTA(CommissionDetail!$N:$N)

como um intervalo nomeado, por exemplo RowCount e, em seguida, a fórmula fica mais curta novamente, ou seja,

=SUM(IFERROR(IF(LEFT(INDIRECT("CommissionDetail!$AL$2:$AL$"&RowCount),13)="As and When (",INDIRECT("CommissionDetail!$AF$2:$AF$"&RowCount)/ INDIRECT("CommissionDetail!$Z$2:$Z$"&RowCount)),0))