Функция СУММЕСЛИ()

[Calvin]

Здраствуйте, уважаемые. Как просчитать с помощью СУММЕСЛИ сумму сразу по нескольким критериям?
Вот так вот не получается, хотя странно:

Код: Выделить всё

=СУММЕСЛИ(A1:A10;ИЛИ("1700";"3000");B1:B10)


[KL]

Здраствуйте, уважаемые. Как просчитать с помощью СУММЕСЛИ сумму сразу по нескольким критериям?
Вот так вот не получается, хотя странно:

Код: Выделить всё

=СУММЕСЛИ(A1:A10;ИЛИ("1700";"3000");B1:B10)


=СУММ(СУММЕСЛИ(A1:A10;{1700;3000};B1:B10))

[anvg]

Calvin
Введи в любую ячейку =ИЛИ("1700";"3000) и посмотри, что возвращает это выражение и что в нём странного

[GSerg]

Забавно...
Если ввести в поиск слово СУММЕСЛИ, то первый же найденный топик содержит тот самый ответ, который приведён KL выше...

По всей видимости, за время долгого отсутствия на форуме Calvin так ничему и не научился.

[Calvin]

Ну во-первых поиск мне ничего не выдал, а во-вторых пример KL работает не корректно, т.к. считает сумму только по одному из критериев .....GSerg ты не прав

[GSerg]

Потому что он немного напутал с символом-разделителем или кавычками, в зависимости от того, в какую сторону исправлять формулу.

Вот тебе и домашнее задание: ввести в поиск слово СУММЕСЛИ и найти корректную версию в первом результате поиска.

Я всё-таки прав.

[KL]

Потому что он немного напутал с символом-разделителем или кавычками, в зависимости от того, в какую сторону исправлять формулу.

Скорее не я напутал, а именно из-за отсутствия файла неясны три (две косвенно указанные GSerg) вещи:

- какой в системе разделитель для строк массива
- действительно ли "1700" и "3000" текст
- если текст, то нет ли в них случайных пробелов или иного мусора

А у меня формула работает очень даже корректно;-)

[Calvin]

GSerg, ну нет в результатах поиска ответа на мой вопрос.....сбрось ссылку плиз, и не устраивай мне домашних заданий

[GSerg]

- какой в системе разделитель для строк массива

Для строковых констант это запятая и точка с запятой, для числовых это двоеточие и точка с запятой, что, в принципе, бредово. Издержки лоКАЛизации.

- действительно ли "1700" и "3000" текст

Применительно к данному случаю однофигственно. Excel разберётся.

- если текст, то нет ли в них случайных пробелов или иного мусора

Не, дело не в этом...

ну нет в результатах поиска ответа на мой вопрос

Ну что можно сказать, значит тест не пройден на пригодность. Раз первого результата поиска в результатах поиска нет.

[KL]

Для...числовых это двоеточие и точка с запятой...

Ну так значит разделитель верный, т.к. столбцы или строки для СУММЕСЛИ значения не имеет.

- действительно ли "1700" и "3000" текст

Применительно к данному случаю однофигственно. Excel разберётся.

Не совсем. Действительно, СУММЕСЛИ разберется в формате, но с текстом больше проблем, одна из которых - третий пункт моего списка.

- если текст, то нет ли в них случайных пробелов или иного мусора

Не, дело не в этом...

???

ну нет в результатах поиска ответа на мой вопрос

См. мои ответы здесь: http://bbs.vbstreets.ru/viewtopic.php?t=32348&start=0

[Calvin]

KL извини, я не увидел вначале СУММ()...все работает, спасибо (я так понял, что сумм() нужен для суммирования по каждому критерию....не сталкивался еще с этим)....GSerg и тебе спасибо

[GSerg]

Ну так значит разделитель верный

Да.
Я почему-то вместо 3000 смотрел 1300.

[KL]

...я так понял, что сумм() нужен для суммирования по каждому критерию....

Да, т.к. СУММЕСЛИ (как и многие др. функции листа) с массивом критериев возвращает массив результатов, которые нужно суммировать.

[KL]

...Я почему-то вместо 3000 смотрел 1300.

Ну все равно проблема оказалась не там, где предполагал каждый из нас

[@Nik]

А можно ещё так:
{=СУММ(((A1:A10=1700)+(A1:A10=3000))*B1:B10)}
или, как в примере у KL с {1700;3000}:
{=СУММ((A1:A10={1700;3000})*B1:B10)}

[Krasnaja Shapka]

еще один ответ (модификация ответа KL в другом топике )

=СУММПРОИЗВ(((A1:A10=1700)+(A1:A10=3000))*B1:B10)

[KL]

А можно ещё так:
{=СУММ(((A1:A10=1700)+(A1:A10=3000))*B1:B10)}
или, как в примере у KL с {1700;3000}:
{=СУММ((A1:A10={1700;3000})*B1:B10)}

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

{=СУММ((A1:A10={1700;3000})*B1:B10)}
[разделитель столбцов массива исправлен по подсказке GSerg с запятой на точку запятой]

еще один ответ (модификация ответа KL в другом топике )

=СУММПРОИЗВ(((A1:A10=1700)+(A1:A10=3000))*B1:B10)

можно еще и так:

=СУММПРОИЗВ((A1:A10={1700;3000})*B1:B10)
[разделитель столбцов массива исправлен по подсказке GSerg с запятой на точку запятой]

только вот все четыре формулы будут на порядок медленнее в диапазоне, где счет строк идет на тысячи У меня в 5,000 строк формула =СУММ(СУММЕСЛИ(A1:A5000;{1700;3000};B1:B5000)) приблизительно в 5 раз быстрее каждого из четырех указанных вариантов

[GSerg]

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

{=СУММ((A1:A10={1700,3000})*B1:B10)}

Хе хе

Запятая -- русская десятичная точка, поэтому 1700,3000 будет воспринято как 1700 целых 3 десятых. Вот здесь и надо двоеточие.
Либо числа в кавычки...

[KL]

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

{=СУММ((A1:A10={1700,3000})*B1:B10)}

Хе хе

Запятая -- русская десятичная точка, поэтому 1700,3000 будет воспринято как 1700 целых 3 десятых. Вот здесь и надо двоеточие.
Либо числа в кавычки...

...!!! ...!!! ...!!! (здесь идет непристойная брань) Ну дайте еще один шанс и преодолею этот пунктуационный идиотизм. Ну право слово, в каждой из 5 локалей, с которыми мне приходится сталкиваться пунктуация не похожа на остальные. Ну не тянет мой PC пять виртуальных машин одновременно

[GSerg]

Ну не тянет мой PC пять виртуальных машин одновременно

Прямо даже как-то неловко объяснять

Пишем формулу в английской локали, выполняем код activecell.formula="=sum(...)" и наслаждаемся формулой, переведённой в русскую локаль

[KL]

Прямо даже как-то неловко объяснять

Пишем формулу в английской локали, выполняем код activecell.formula="=sum(...)" и наслаждаемся формулой, переведённой в русскую локаль

Ну и отлично: невеста согласна, осталось всего лишь Рокфеллера уговорить! Хорошо тебе говорить, так для этого нужно как минимум находиться в русской локали Я работаю с Windows и MSO на английском. MUI пришлось снести из экономии места на диске. Вот и приходиться загружать VMWare с локальными версиями всякий раз как надо тестировать решения.

[@Nik]

Чёт я не понял с вашими разделителями... ?? {=СУММ((A1:A10={1700:3000})*B1:B10)}
У меня правильно отрабатывается только ";". Даже когда в параметрах на вкладке "Международные" разделитель рядов явно задал ":", всё равно не работало.

[KL]

Чёт я не понял с вашими разделителями... ?? {=СУММ((A1:A10={1700:3000})*B1:B10)}
У меня правильно отрабатывается только ";". Даже когда в параметрах на вкладке "Международные" разделитель рядов явно задал ":", всё равно не работало.

В приложенном файле [A1] и [B1] содержат горизонтальные, а [A2] и [B2] вертикальные массивы. Какие разделители видишь ты?

[@Nik]

Горизонтальные - ";"
Вертикальные - ":"

[KL]

Горизонтальные - ";"
Вертикальные - ":"

тогда прав ты с разделителем ";"

[@Nik]

Столкнулся с одной из модификаций данной задачи: проссумировать те значения в диапазоне А1:А10, которые больше 5 но меньше 10. Что-то с помощью СУММЕСЛИ() не получается никак составить... Единственный синтаксис, который принимается, это:
{=СУММЕСЛИ(ИНДЕКС((A1:A10>5)*A1:A10;);"<10")}, но он не хотит работать
Единственный вариант, который мне удалось придумать - это:
{=СУММ((A1:A10>5)*(A1:A10<10)*A1:A10)}

Есть ли ещё какие-либо интересные способы..... ?

[KL]

Столкнулся с одной из модификаций данной задачи: проссумировать те значения в диапазоне А1:А10, которые больше 5 но меньше 10. Что-то с помощью СУММЕСЛИ() не получается никак составить... Единственный синтаксис, который принимается, это:
{=СУММЕСЛИ(ИНДЕКС((A1:A10>5)*A1:A10;);"<10")}, но он не хотит работать
Единственный вариант, который мне удалось придумать - это:
{=СУММ((A1:A10>5)*(A1:A10<10)*A1:A10)}

Есть ли ещё какие-либо интересные способы..... ?

Самый эффективный:

=СУММЕСЛИ(A1:A10;">5")-СУММЕСЛИ(A1:A10;">=10")

С извращением (слегка покороче):

=СУММ(СУММЕСЛИ(A1:A10;{">5";">=10"})*{1;-1})

P.S. функция ИНДЕКС с массивами не работает

[kopai]

Здравствуйте!
Подскажите, как применить функцию СУММЕСЛИ одновременно для нескольких диапазонов?... т.е. данные в ячейке суммируются только те, которые соответствуют критерию№1 по одному диапазону и критерию№2 по другому диапазону ОДНОВРЕМЕННО...

[Pavel55]

Точно не знаю, но думаю так, как пример

=СУММПРОИЗВ(($A$2:$A$20=10)*($B$2:$B$20=20)*$D$2:$D$20)

т.е. ещем 10 в столбце А и 20 в столбце В и суммируем столбец D

[kopai]

Точно не знаю, но думаю так, как пример

=СУММПРОИЗВ(($A$2:$A$20=10)*($B$2:$B$20=20)*$D$2:$D$20)

т.е. ещем 10 в столбце А и 20 в столбце В и суммируем столбец D

работает в виде
=СУММПРОИЗВ(($A$2:$A$20=10)*($B$2:$B$20=20);$D$2:$D$20)
т.е. ";" вместо "*"
СПАСИБО!!!