Формулы вложенных массивов

Чтобы сделать это за один шаг с помощью формулы массива, вы можете использовать:

=SUM(IFERROR((D66:D86*(F66:F86="Closed"))/((C66:C86=TRANSPOSE(C66:C86))*TRANSPOSE(D66:D86*(F66:F86="Partial Sold"))),0))

Это формула массива, и ее необходимо подтвердить с помощью Ctrl + Shift + Enter↵.

он сгенерирует 2D-массив, содержащий исходные значения для закрытых как строк, и разделит этот 1D-массив следующим образом:

• строит 2D-массив по столбцу C = транспонированному столбцу C
• умножьте каждую строку на столбец D
• установите для всех элементов в каждой строке значение 0, если не "Частичная продажа"

для каждого div на 0 IFERROR установит его в 0
и все это в SUM даст вам ваш результат

Я бы тоже рекомендовал использовать вспомогательный столбец. Однако вам не нужно использовать формулы массива, чтобы найти ответ. Вы можете использовать следующее в следующем доступном столбце:

=IF(F66="Closed",IFERROR(D66/SUMIFS($D$66:$D$86,$F$66:$F$86,"Partial Sold",$C$66:$C$86,C66),0),0)

Это вернет значения для всего, что соответствует вашим критериям, и нули для всего остального. Затем вы можете просто взять сумму этого вспомогательного столбца как окончательную сумму ставок.

Если вы действительно не хотите использовать вспомогательный столбец, вы можете заключить формулу вспомогательного столбца в SUM и заменить отдельные ссылки на ячейки на массивы (например, поменять местами F66 на $F$66:$F$86 и т. Д.) , затем введите его как формулу массива с помощью Ctrl + Shift + Enter↵. Все это выглядело бы так:

=SUM(IF($F$66:$F$86="Closed",IFERROR($D$66:$D$86/SUMIFS($D$66:$D$86,$F$66:$F$86,"Partial Sold",$C$66:$C$86,$C$66:$C$86),0),0))

Я не вижу, чтобы это делалось без вспомогательной колонки. В неиспользуемый столбец справа от F66 поместите эту формулу массива¹.

=IF(AND(OR(C66=INDEX(C$66:C$86*(F$66:F$86="Partial Sold"), , )), F66="Closed"), D66/INDEX(D$66:D$86, AGGREGATE(15, 6, ROW($1:$21)/((C$66:C$86=C66)*(F$66:F$86="Partial Sold")), 1)), "")

При необходимости долейте. Результатом будет сумма этих «вспомогательных» чисел.

Даже если бы это можно было сделать с помощью одной формулы, накладные расходы на вычисления, вероятно, были бы непомерно высокими. Разделение части вычислений массива на вспомогательный столбец, который может напрямую ссылаться на значение в столбце C для другого поиска, значительно снижает это.

¹ _{Формулы массива должны быть завершены с помощью Ctrl + Shift + Enter↵. После правильного ввода в первую ячейку их можно заполнить или скопировать вниз или вправо, как и любую другую формулу. Попробуйте сократить количество ссылок на весь столбец до диапазонов, более точно представляющих объем ваших фактических данных. Формулы массива логарифмически перебирают циклы вычислений, поэтому рекомендуется сузить указанные диапазоны до минимума. См. Рекомендации и примеры формул массива для получения дополнительной информации.}