Вычисление СУММПРОИЗВ с использованием критериев нескольких столбцов и критериев одной строки (с пустыми столбцами и текстовыми столбцами в диапазоне данных)
| A |B|C |D| E |F| G | H | I | J |K| L | M | N |
--|-------|-|--|-|----------|-|--------|------|------|------|-|-------|--------|--------|--
1 | | | | | ratio | | tested | 2023 | 2024 | 2025 | | | | |
2 | | | | | | | | | | | | | | |
3 | | | | |Prod.Line1| | | | | | |RowCrit|ColCrit1|ColCrit2|
4 |Brand A| |P1| | 7% | | yes | 500 | 70 | 60 | | 2024 | Brand A| P1 |
5 |Brand A| |P2| | 8% | | yes | 100 | 47 | 300 | | | Brand D| P4 |
6 |Brand A| |P2| | 10% | | yes | 800 | 21 | 200 | | | | |
7 |Brand B| |P1| | 5% | | yes | 90 | 56 | 150 | | | | |
8 |Brand C| |P4| | 3% | | no | 45 | 700 | 790 | | | | |
9 |Brand C| |P2| | 8% | | no | 600 | 150 | 40 | | Result| 191.5 | |
10| | | | |Subtotal | | | | | | | | | |
11| | | | | | | | | | | | | | |
12| | | | |Prod.Line2| | | | | | | | | |
13|Brand D| |P1| | 12% | | yes | 900 | 90 | 980 | | | | |
14|Brand D| |P1| | 20% | | yes | 125 | 854 | 726 | | | | |
15|Brand D| |P3| | 19% | | yes | 70 | 860 | 614 | | | | |
16|Brand D| |P4| | 2% | | yes | 842 | 250 | 85 | | | | |
17|Brand E| |P4| | 6% | | no | 300 | 324 | 450 | | | | |
18| | | | |Subtotal | | | | | | | | | |
19| | | | | | | | | | | | | | |
Я хочу вычислить SUMPRODUCT, умножив значения из Column E на соответствующие значения в Column H:J.
Формула SUMPRODUCT также должна учитывать фильтры для Row в Cell L4 и Column в Range M4:M5 и Range N4:N5.
В примере выше результат:
Cell M9 = 7%x70 + 12%x90 + 20%x854 + 2%x250 = 191.5
Что касается ответов в на этот вопрос, я попытался применить это решение:
=LET(
a, IF(
COUNTA($M$4:$M$5)=0,
N(ISNUMBER(ROW($A$2:$A$19))),
COUNTIF($M$4:$M$5,$A$2:$A$19)
),
b, IF(
COUNTA($N$4:$N$5)=0,
N(ISNUMBER(ROW($C$2:$C$19))),
COUNTIF($N$4:$N$5,$C$2:$C$19)
),
c, CHOOSECOLS($A$2:$J$19,XMATCH($E$1,$A$1:$J$1),XMATCH($L$4,$A$1:$J$1)),
SUM(a*b*CHOOSECOLS(c,1)*CHOOSECOLS(c,2))
)
Однако в результате он возвращает #VALUE.
Насколько я могу судить, это вызвано заголовками Prod.Line1 и Prod.Line 2 в Cell E3 и Cell E12.
Есть ли у вас идеи, как изменить формулу, чтобы она работала?
Ответы 3
Попробуйте следующее: (возможно, вам придется изменить ; на в качестве разделителя значений функции - это зависит от региона)
=SUMPRODUCT((H1:J1=L4)*(A4:A17=M4)*(C4:C17=N4)*(I4:I17)*IF(ISNUMBER(E4:E17);E4:E17;0)+(H1:J1=L4)*(A4:A17=M5)*(C4:C17=N5)*(I4:I17)*IF(ISNUMBER(E4:E17);E4:E17;0))
Что касается комментария rachel в этого вопроса, решение следующее:
=SUMPRODUCT(LET(a, COUNTIF(M4:M6,A3:A19),b,COUNTIF(N4:N6,C3:C19),FILTER(FILTER(E3:J19,(E1:J1=L4),""),IFS(SUM(a)=0,b,SUM(b)=0,α,1,a*b),"")),LET(a, COUNTIF(M4:M6,A3:A19),b,COUNTIF(N4:N6,C3:C19),FILTER(E3:E19,IFS(SUM(a)=0,b,SUM(b)=0,α,1,a*b),"")))
попробуйте это, то же самое, что и другое, опубликованное вчера ранее, поделитесь отзывами: =LET(α, A2:J17,δ, COUNTIFS(M4:M6,TAKE(α,,1),M4:M6,"<>"),ε, COUNTIFS(N4:N6,INDEX(α,,3),N4:N6,"<>"),φ, IFS(SUM(δ)=0,ε,SUM(ε)=0,δ,1,δ*ε),Σ, CHOOSECOLS(FILTER(α, (IF(SUM(φ)=0,SEQUENCE(ROWS(α))^0,φ))*(1-ISERR(--INDEX(α,,5))),0),5,XMATCH(L4,A1:J1)),SUM(TAKE(Σ,,1)*TAKE(Σ,,-1)))
Это работает с моей стороны на основе предоставленных условий:
• Формула, используемая в ячейке M8
=LET(
α, A2:J17,
δ, COUNTIFS(M4:M6,TAKE(α,,1),M4:M6,"<>"),
ε, COUNTIFS(N4:N6,INDEX(α,,3),N4:N6,"<>"),
φ, IFS(SUM(δ)=0,ε,SUM(ε)=0,δ,1,δ*ε),
Σ, CHOOSECOLS(FILTER(α, (IF(SUM(φ)=0,SEQUENCE(ROWS(α))^0,φ))*(1-ISERR(--INDEX(α,,5))),0),5,XMATCH(L4,A1:J1)),
SUM(TAKE(Σ,,1)*TAKE(Σ,,-1)))
На основе моего оригинального решения Вычислить СУММПРОИЗВ с критериями нескольких столбцов и критериями одной строки (с пустыми столбцами в диапазоне данных)
Тестовый пример первый: --> Когда ColCrit1 пусто
Тестовый пример второй: --> Когда ColCrit2 пусто
Третий тестовый пример: --> Когда ColCrit1 и ColCrit2 оба пусты
Связано ли это с stackoverflow.com/questions/78537715/… ? потому что похоже, что вы можете просто фильтровать столбец E на основе критериев столбца, фильтровать столбец H:J на основе критериев строки и столбца, а затем вычислять сумму произведений????