Суммирование диапазона с несколькими условными исключениями

Страница 1 из 1 1
Модератор форума: китин, _Boroda_, DrMini

Суммирование диапазона с несколькими условными исключениями (Формулы/Formulas)

dickist

Дата: Суббота, 06.06.2015, 11:49 | Сообщение № 1

Группа: Пользователи

Ранг: Новичок

Сообщений: 11

Репутация: 0 ±

Замечаний: 0% ±

Excel 2010

Здравствуйте!
Нужно суммировать значения диапазона В2:В8, кроме тех, которые соответствуют значениям "в ожидании" ИЛИ "от агента007" из диапазона А2:А8; или "наоборот":
суммировать только значения диапазона В2:В8, которые соответствуют значениям "списано" ИЛИ "получено" из диапазона А2:А8.
Пример прилагаю.
Для случая с одним условным исключением из суммирования работает такая формула:

Код

=СУММ(ЕСЛИ(A2:A8="в ожидании";0;B2:B8))

Попытался сделать по аналогии для нескольких исключений из суммирования:

Код

=СУММ(ЕСЛИ(ИЛИ(A2:A8="в ожидании";A2:A8="от агента007");0;B2:B8))

или "наоборот"

Код

=СУММ(ЕСЛИ(ИЛИ(A2:A8="списано";A2:A8="получено");B2:B8;0))

Обе формулы не работают
Подскажите, пожалуйста, как решить такую задачу.

К сообщению приложен файл: 1306845.xls (26.5 Kb)

Ответить

buchlotnik

Дата: Суббота, 06.06.2015, 11:59 | Сообщение № 2

Группа: Заблокированные

Ранг: Участник клуба

Сообщений: 3442

Репутация: 929 ±

Замечаний: 20% ±

2010, 2013, 2016 RUS / ENG

если "в лоб", то немассивка

Код

=СУММПРОИЗВ((A2:A8="в ожидании")*B2:B8)+СУММПРОИЗВ((A2:A8="от агента007")*B2:B8)

К сообщению приложен файл: 9243330.xls (27.5 Kb)

Ответить

KSV

Дата: Суббота, 06.06.2015, 12:00 | Сообщение № 3

Группа: Друзья

Ранг: Ветеран

Сообщений: 770

Репутация: 255 ±

Замечаний: 0% ±

Excel 2013

Цитата dickist, 06.06.2015 в 11:49, в сообщении № 1

Попытался сделать по аналогии для нескольких исключений из суммирования:

можно так:

Код

=СУММ(ЕСЛИ((A2:A8="в ожидании")+(A2:A8="от агента007");0;B2:B8))

с остальными - аналогично

KSV.VBA@gmail.com
Яндекс.Деньги: 410011921213333

Ответить

Russel

Дата: Суббота, 06.06.2015, 12:02 | Сообщение № 4

Группа: Друзья

Ранг: Старожил

Сообщений: 1394

Репутация: 320 ±

Замечаний: 0% ±

Excel 2010

Добрый день!
Из общей суммы вычитать исключения:

Код

=СУММ(B2:B8)-СУММЕСЛИ(A2:A8;"в ожидании";B2:B8)-СУММЕСЛИ(A2:A8;"от агента007";B2:B8)

или через СУММПРОИЗВ():

Код

=СУММПРОИЗВ((A2:A8<>"в ожидании")*(A2:A8<>"от агента007")*B2:B8)

или массивная:

Код

=СУММ((A2:A8<>"в ожидании")*(A2:A8<>"от агента007")*B2:B8)

QIWI 9173973973

Сообщение отредактировал Russel - Суббота, 06.06.2015, 12:08

Ответить

AndreTM

Дата: Суббота, 06.06.2015, 12:08 | Сообщение № 5

Группа: Друзья

Ранг: Старожил

Сообщений: 1762

Репутация: 501 ±

Замечаний: 0% ±

2003 & 2010

Ну и еще (учитывая 2010 у ТС) забыли про

Код

=СУММЕСЛИМН(B2:B8;A2:A8;"<>в ожидании";A2:A8;"<>от агента007")

Skype: andre.tm.007
Donate: Qiwi: 9517375010

Ответить

dickist

Дата: Суббота, 06.06.2015, 12:24 | Сообщение № 6

Группа: Пользователи

Ранг: Новичок

Сообщений: 11

Репутация: 0 ±

Замечаний: 0% ±

Excel 2010

Большое Спасибо! buchlotnik, KSV, Russel, и AndreTM,
Буду разбираться.

Ответить

dickist

Дата: Суббота, 06.06.2015, 15:31 | Сообщение № 7

Группа: Пользователи

Ранг: Новичок

Сообщений: 11

Репутация: 0 ±

Замечаний: 0% ±

Excel 2010

AndreTM, еще раз Спасибо!
НО я пробовал аналогичную формулу,

Код

=СУММЕСЛИМН(B2:B8;A2:A8;"=списано";A2:A8;"=получено")

хотя и думал, что она не должна работать, и она НЕ Работает, т.к. в СУММЕСЛИМН все условия по принципу И, а не ИЛИ, т.е. должны присутствовать ОДНОВРЕМЕННО, а у меня или одно, или другое.
Почему же тогда работает Ваша формула

Код

=СУММЕСЛИМН(B2:B8;A2:A8;"<>в ожидании";A2:A8;"<>от агента007")

Помогите, пожалуйста понять в чем их принципиальное различие?

Ответить

buchlotnik

Дата: Суббота, 06.06.2015, 15:43 | Сообщение № 8

Группа: Заблокированные

Ранг: Участник клуба

Сообщений: 3442

Репутация: 929 ±

Замечаний: 20% ±

2010, 2013, 2016 RUS / ENG

в том, что во втором случае мы суммируем по условию "любое значение, кроме указанных" - это оператор И (не равно первому, не равно второму и т.д.); а вот в первом случае вы хотите впихнуть оператор ИЛИ, что противоречит парадигме самой формулы, точнее вы прописываете И(), а хотите чтоб работало как ИЛИ()

Сообщение отредактировал buchlotnik - Суббота, 06.06.2015, 15:49