Usando o COUNTIFS para uma série de valores de uma só vez

Question

Trabalhando mais alto do que o COUNTIFS, aponto um desafio para escrever uma fórmula sem código VBA. Os dados básicos são combinados a partir de milhares de linhas com:

• Coluna A: linhas com valores de 1 a 3
• Coluna B: linhas com valores de 1 a 250.

Para esse propósito, digamos, estamos analisando todas as células de valor "1" na coluna A, que correspondem ao valor "5" na coluna B. Para encontrar todas as correspondências, usamos o comando COUNTIFS.

1   1
2   5
1   5
1   7
1   10
3   45
2   12
1   2
2   1

=COUNTIFS(A1:A9;1;B1:B9;5)

A resposta aqui é 1.

A seguir, o "5" na coluna B pertence a um grupo, e. grupo de 1 a 9. Qual seria a melhor maneira de contar todas as correspondências neste exemplo, de modo que para todos os "1" na coluna A, teríamos que encontrar todas as correspondências com valores de 1 a 9 em coluna B ?! No exemplo superior, isso resultaria em "4". A solução óbvia é com uma série de comandos IF, mas isso é ineficiente e é fácil cometer um erro, que é facilmente supervisionado.

=COUNTIFS(A1:A9;1;B1:B9;"<="&9)

Funciona apenas como limite superior. Se eu der a faixa e a condição do terceiro critério como ">=" & 1, isso não funcionará - retorna 0.

Percebi que três intervalos e condições de critérios funcionam apenas se eu usar "=" em uma condição. Assim que eu usar

=COUNTIFS(A1:A9;1;B1:B9;"<="&9,B1:B9;">=1")

retorna 0. Meu objetivo é eventualmente substituir o número em uma condição por um comando VLOOKUP, então a equação final deve ser igual a

=COUNTIFS(A1:A9;1;B1:B9;"<="&VLOOKUP(...),B1:B9;">=VLOOKUP(...)")

Mas o "<" e ">" Sinais bagunçam isso. Ainda procurando uma solução.

Gasper

    

Answer 1

Hmmm. Estranho e não conforme especificado pela Microsoft. Tentei reproduzir o problema, mas no Excel 2011 para Mac e no Excel 2010 para Windows ele funciona como esperado usando a seguinte fórmula: =COUNTIFS(A1:A9;E1;B1:B9;">="&E2;B1:B9;"<="&E3) (usei três células para os valores de pesquisa que você deseja usar).

    

Answer 2

Obrigado @agtoever! Funciona, acabei de perceber que meus problemas estão em outro lugar. Este é todo o meu código:

=COUNTIFS(INDIRECT(CONCATENATE("baza!$";SUBSTITUTE(ADDRESS(1;MATCH("card_type_id";baza!$A$1:$AAA$1;0);4);"1";"");"$2:$";SUBSTITUTE(ADDRESS(1;MATCH("card_type_id";baza!$A$1:$AAA$1;0);4);"1";"");"$15000"));1;INDIRECT(CONCATENATE("baza!$";SUBSTITUTE(ADDRESS(1;MATCH(IF($C$4="CC_SI_klasifikacija";"building_classification_id";0);baza!$A$1:$AAA$1;0);4);"1";"");"$2:$";SUBSTITUTE(ADDRESS(1;MATCH(IF($C$4="CC_SI_klasifikacija";"building_classification_id";0);baza!$A$1:$AAA$1;0);4);"1";"");"$15000"));"<="&VLOOKUP(C5;K203:N210;4;FALSE))

O problema é que meu segundo intervalo de critérios não é reconhecido como números nas células (figura abaixo). Se você olhar para a fórmula - esta é a coluna N. É possível converter o texto em um número dentro de uma fórmula? por exemplo. NUMBERVALUE?

link