Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Несколько условий с диапазоном в функции СУММЕСЛИМН
 
#1
30.06.2022 16:21:26
Доброго времени суток! Имеется таблица с 2 переменными (столбцы id и type) и суммой по ним (столбец sum), значения в которых могут быть одинаковыми. Необходимо просуммировать ячейки со столбца sum с несколькими условиями в диапазонах. Формула СУММПРОИЗВ(СУММЕСЛИМН...  не работает если количество и индекс элементов в диапазоне условия 1 не совпадает с количеством и индексами в диапазоне условия 2.
 
 
 
#2
30.06.2022 16:36:38
Проверил критерии и соответствующие им числа вручную с помощью фильтра, вроде все у вас правильно, или я чего-то не понимаю?
 
 
 
#3
30.06.2022 16:42:58
Симон Боливар,
во вложении один из вариантов как это можно сделать.
В основном используется формула ФИЛЬТР.

Также ниже сама формула.
Если хотите добавить еще критерий - то нужно в формулу фильтра добавить еще умножение.
ФИЛЬТР так и работает - в больших скобках стоит совокупное условие по первому и второму критерию (через плюсики), а потом мы ищем значения которые совпадают с обоими (через умножение). Добавлять условий можно сколько угодно.

Скрытый текст
 
 
 
#4
30.06.2022 16:44:51
Цитата
написал:
Проверил критерии и соответствующие им числа вручную с помощью фильтра, вроде все у вас правильно, или я чего-то не понимаю?
Нужно чтобы сумма выходила по всем строкам содержащим указанные id и type. На примере выборки 1 нужна сумма всех строк с id =1000,1001 и type=0,4. Если через фильтр оставить строки с этими критериями. сумма оставшихся строк равна 734 как в ячейке G2
 
 
 
#5
30.06.2022 16:45:05
Что бы процессор не скучал :)
=SUMPRODUCT((($A$10:$A$208=K2)+($A$10:$A$208=L2)+($A$10:$A$208=M2))*(($B$10:$B$208=O2)+($B$10:$B$208=P2)+($B$10:$B$208=Q2)+($B$10:$B$208=R2))*($C$10:$C$208))
тольео в type? не должно быть пустых ячеек, можно пустые пробелами добить
 
 
 
#6
30.06.2022 16:49:42
bigorq,
Попробовал Ваш вариант - но с пробелами не очень удобно как по мне.
По сути тот же самый вариант, что я описал выше.
 
 
 
#7
30.06.2022 17:01:23
Pavel Dickenson, не у всех есть новый офис, а без фильтр() формулы считают одинаково и пустые значения не любят.
 
 
 
#8
30.06.2022 17:02:36
Цитата
написал:
Симон Боливар,
во вложении один из вариантов как это можно сделать.
В основном используется формула ФИЛЬТР.

Также ниже сама формула.
Если хотите добавить еще критерий - то нужно в формулу фильтра добавить еще умножение.
ФИЛЬТР так и работает - в больших скобках стоит совокупное условие по первому и второму критерию (через плюсики), а потом мы ищем значения которые совпадают с обоими (через умножение). Добавлять условий можно сколько угодно.

    Скрытый текст       =СУММПРОИЗВ(ФИЛЬТР($C$10:$C$208;(($A$10:$A$208=L2)+($A$10:$A$208=M2)+($A$10:$A$208=N2))*(($B$10:$B$208=P2)+($B$10:$B$208=Q2)+($B$10:$B$208=R2)+($B$10:$B$208=S2))))
Решение красивое, но на работе используется excel 2016. Спасибо за вариант.
Цитата
написал:
Что бы процессор не скучал
=SUMPRODUCT((($A$10:$A$208=K2)+($A$10:$A$208=L2)+($A$10:$A$208=M2))*(($B$10:$B$208=O2)+($B$10:$B$208=P2)+($B$10:$B$208=Q2)+($B$10:$B$208=R2))*($C$10:$C$208))
тольео в type? не должно быть пустых ячеек, можно пустые пробелами добить
Спасибо, попробую в деле.  
 
 
 
#9
30.06.2022 18:06:07
Держите решение.
=SUMPRODUCT(ISNUMBER(MATCH($A$10:$A$208;$K2:$M2;0))*ISNUMBER(MATCH($B$10:$B$208;$O2:$R2;0));$C$10:$C$208)
Ваша формула с суммеслимн считает попарно по условиям (т.е. 1000 = 0 и 1001 = 4 и т.д.), что собственно поначалу и ввело меня в заблуждение.
 
 
 
#10
30.06.2022 18:24:39
Цитата
написал:
Держите решение.
=SUMPRODUCT(ISNUMBER(MATCH($A$10:$A$208;$K2:$M2;0))*ISNUMBER(MATCH($B$10:$B$208;$O2:$R2;0));$C$10:$C$208)
Ваша формула с суммеслимн считает попарно по условиям (т.е. 1000 = 0 и 1001 = 4 и т.д.), что собственно поначалу и ввело меня в заблуждение.
Подходит идеально! Спасибо
 
 
 
#11
30.06.2022 19:24:15
ну и еще один вариант, короче, правда не знаю, как там с производительностью.
=SUMPRODUCT(COUNTIF($K2:$M2;$A$10:$A$208)*COUNTIF($O2:$R2;$B$10:$B$208);$C$10:$C$208)
 
 
 
#12
30.06.2022 20:44:58
Симон Боливар,  прекратите использовать цитирование, если не понимаете что это!
№8 там все в цитате так важно? А если б там пояснение в трех томах б было, то процитировали б от первого до последнего?
Вернитесь и скорректируйте сообщения.

Цитата
Симон Боливар написал:
Формула СУММПРОИЗВ(СУММЕСЛИМН...  не работает
почти работает, но только при двух параметрах не более и и это не самый быстрый вариант.
Массивная =SUM(SUMIFS(C$9:C$209;A$9:A$209;IF(K2:M2<>"";K2:M2);B$9:B$209;TRANSPOSE(IF(O2:R2<>"";O2:R2))))
По вопросам из тем форума, личку не читаю.
 
 
 
Страницы: 1
Наверх