Вычисление СУММПРОИЗВ с несколькими критериями столбца и критериями одной строки

Пытаться:

=LET(
    a, COUNTIF($J$2:$J$3, $A$2:$A$12),
    b, COUNTIF($K$2, $B$2:$B$12),
    c, CHOOSECOLS($A$2:$G$12, 3, XMATCH($I$2, $A$1:$G$1)),
    SUM(a * b * CHOOSECOLS(c, 1) * CHOOSECOLS(c, 2))
)

Чтобы учесть отсутствие записей в ColCrit1 или отсутствие записей в ColCrit2, вы можете изменить его:

=LET(
    a, IF(
        COUNTA($J$2:$J$5) = 0,
        N(ISNUMBER(ROW($A$2:$A$12))),
        COUNTIF($J$2:$J$5, $A$2:$A$12)
    ),
    b, IF(
        COUNTA($K$2:$K$5) = 0,
        N(ISNUMBER(ROW($B$2:$B$12))),
        COUNTIF($K$2:$K$5, $B$2:$B$12)
    ),
    c, CHOOSECOLS($A$2:$G$12, 3, XMATCH($I$2, $A$1:$G$1)),
    SUM(a * b * CHOOSECOLS(c, 1) * CHOOSECOLS(c, 2))
)

Итак, попробуйте реализовать следующее, чтобы получить желаемые результаты, я не был уверен, что индекс столбца ratio должен быть здесь жестко запрограммирован:

• Использование вспомогательной функции LAMBDA() под названием BYROW().

=LET(
     α, A2:G12,
     δ, COUNTIF(J2:J3,TAKE(α,,1)),
     ε, COUNTIF(K2:K3,INDEX(α,,2)),
     φ, IFS(SUM(δ)=0,ε,SUM(ε)=0,δ,1,δ*ε),
     SUM(BYROW(CHOOSECOLS(FILTER(α, φ),3,XMATCH(I2,A1:G1)),LAMBDA(a, PRODUCT(a)))))

• Или без использования вспомогательных функций LAMBDA():

=LET(
     α, A2:G12,
     δ, COUNTIF(J2:J3,TAKE(α,,1)),
     ε, COUNTIF(K2:K3,INDEX(α,,2)),
     φ, IFS(SUM(δ)=0,ε,SUM(ε)=0,δ,1,δ*ε),
     Σ, CHOOSECOLS(FILTER(α, φ),3,XMATCH(I2,A1:G1)),
     SUMPRODUCT(TAKE(Σ,,1)*TAKE(Σ,,-1)))

Вариант использования первый: --> Когда ColCrit1 пусто

Вариант использования второй: --> Когда ColCrit2 пусто

Аналогичная идея взята из последнего ответа на сообщение ОП: Использование списка (диапазона) критериев столбца (а не отдельных ячеек) в формуле ФИЛЬТР

Еще один способ использования Custom LAMBDA() + MMULT():

=LET(
     λ,LAMBDA(α,δ,ABS(AND(ISBLANK(δ))-MMULT(--(α=TOROW(δ)),{1;1}))),
   SUM(FILTER(E2:G12,E1:G1=I2)*C2:C12*λ(A2:A12,J2:J3)*λ(B2:B12,K2:K3)))

Версия с использованием SUMPRODUCT, COUNTIF и FILTER и с

• Критерии в нескольких ячейках. Обычная функциональность доступна для критериев COUNTIF.
• Включите все, если все пробелы в названиях брендов или продуктов
• Включить все годы, если критерии пусты

Рассчитайте результат с помощью:

=SUMPRODUCT(
_filter_by_year,
_ratios,
_include(_brands, _col_crit_1),
_include(_products, _col_crit_2))

Создайте следующие именованные диапазоны и функции (они будут доступны на всех листах книги):