Excel: Célula suspensa de referência na fórmula de contagem

Question

Estou tentando substituir o texto em uma fórmula do Excel por uma referência de célula ao texto em uma lista suspensa.

A primeira fórmula que produz a resposta correta é:

=(SUM(COUNTIFS('LP - All Properties'!G:G,{"Phoenix","Tucson"},'LP - All Properties'!L:L,"Leased")))/SUM(COUNTIF('LP - All Properties'!G:G,{"Phoenix","Tucson"}))

Eu quero substituir o texto "Phoenix" e "Tucson" por uma referência de célula a uma lista suspensa contendo esses valores. No entanto, esta fórmula não produz o resultado adequado:

=(SUM(COUNTIFS('LP - All Properties'!G:G,{"C2","D2"},'LP - All Properties'!L:L,"Leased")))/SUM(COUNTIF('LP - All Properties'!G:G,{"C2","D2"}))

Eu também tentei sem as aspas em torno de C2 e D2, mas isso também não funcionou.

    

Answer 1

O parâmetro C2: D2 forma uma matriz e, portanto, você precisa torná-la uma função de matriz. A fórmula correta seria

=(SUM(COUNTIFS('LP - All Properties'!G:G,C2:D2,'LP - All Properties'!L:L,"Leased")))/SUM(COUNTIF('LP - All Properties'!G:G,C2:D2))

E aí vem a parte complicada - em vez de pressionar Enter para concluir sua entrada, você precisará pressionar Ctrl + Shift + Enter para informar ao Excel que ele contém o parâmetro para a função array.

Se você conseguir fazer isso corretamente, a fórmula será parecida com {=(SUM(COUNTIFS(...))} com o parêntese {} ao redor da fórmula.

    

Answer 2

Para uma abordagem um pouco diferente, você pode usar AVERAGE , ou seja, essa "fórmula de matriz"

=AVERAGE(IF('LP - All Properties'!G:G=C2:D2,IF('LP - All Properties'!L:L="Leased",1,0)))

confirmado com CTRL + SHIFT + ENTER

Isso só funciona quando C2:D2 é um vetor horizontal