Excel: ссылочная выпадающая ячейка в формуле Countif

Question

Я пытаюсь заменить текст в формуле Excel ссылкой на текст в раскрывающемся списке.

Первая формула, которая дает правильный ответ:

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

Я хочу заменить текст "Феникс" и "Тусон" ссылкой на ячейку для раскрывающегося списка, содержащего эти значения. Однако эта формула не дает правильного результата:

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

Я также пытался без кавычек вокруг C2 и D2, но это также не сработало.

Answer 1

Параметр C2:D2 формирует массив, поэтому вам нужно сделать его функцией массива. Правильная формула будет

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

И тут начинается сложная часть: вместо того, чтобы нажимать Enter, заканчивая ввод, вам нужно будет нажать Ctrl+Shift+Enter, чтобы Excel знал, что он содержит параметр для функции массива.

Если вам удастся сделать это правильно, формула будет выглядеть как {=(SUM(COUNTIFS(...))} с круглыми скобками {} вокруг формулы.

Answer 2

Для немного другого подхода вы можете использовать AVERAGE , то есть эту "формулу массива"

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

подтверждено с помощью CTRL+SHIFT+ENTER

Это работает только тогда, когда C2:D2 является горизонтальным вектором