Почему формула суммирования не работает в Excel

Обновлено: 21.11.2024

В чем проблема с этой формулой Excel СУММЕСЛИМН

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

Была ли у вас когда-нибудь такая проблема с функцией Excel?

Эта формула представляла собой простую (или, по крайней мере, мне казалось простой) функцию SumIF, которая использовала относительные ссылки, абсолютные ссылки и ссылку на таблицу Excel в очень большом диапазоне.

В чем проблема с этой формулой Excel СУММЕСЛИМН

Обычно я использовал ленту "Формулы", а затем нажимал кнопку "Оценить формулу", чтобы отследить свою проблему, однако из-за длины диапазона и ошибок я не мог найти точную проблему. Должен признаться, что я не знал об этом ВЕЛИКОЛЕПНОМ инструменте несколько лет назад, и мне жаль, что я не знал намного раньше. Мне придется опубликовать видео, показывающее, как его использовать, если вы не знакомы.

Итак, я воспроизвел формулу с помощью функции Excel SumProduct (что, на мой взгляд, прекрасно), но я не думал, что клиент сможет поддерживать эту функцию самостоятельно. Поэтому мне нужно было решить проблему с SumIF.

Я попытался разбить его на части и просто ввел 1 критерий, подтверждающий правильность названия. и это работает.

Затем я добавил еще один критерий, который добавил компонент даты в формулу, чтобы проверить, что дата была >= значением, и это не удалось.

Поэтому я упростил критерий формулы 2, чтобы проверить конкретную дату, и это сработало.

Значит, я что-то делаю не так. Давайте добавим обратно в условие >=.

Вот моя формула:

Вы видите, что не так? Я этого не делал, но знаю, что это второй критерий, раздел «>=B$2″».

Итак, я попробовал это:

=СУММЕСЛИМН(Таблица1[Сумма],Таблица1[Категория],$A120,Таблица1[Дата]»,>=»B$2) [Я только что переместил правую кавычку «» перед B$2. Это должно сработать, верно? Но НЕТ!

Затем я проверяю справку Microsoft Excel:

Вот их примеры:

Синтаксис

СУММЕСЛИМН(сумма_диапазон, критерий_диапазон1, критерий1, [критерий_диапазон2, критерий2], …)

=СУММЕСЛИМН(A2:A9,B2:B9, "=A*", C2:C9, "Том")
=СУММЕСЛИМН(A2:A9,B2:B9",<>Бананы",C2:C9,"Том")

Ничего подобного я не делаю. Они проверяют только точные значения, а не значение ячейки.

Никакой помощи в моей проблеме.

Мне не хватает амперсанда ‘&’

Вот как должна выглядеть формула:

Вы видите проблему?

У меня не было соединения ссылки на ячейку и условного оператора. Я пытался объединить их все в одной области, поместив их в кавычки или просто после кавычек. Мне никогда не приходило в голову, что мне нужно соединить их с оператором амперсанда, но теперь это имеет смысл. Думаю, мне нужно больше использовать эти формулы 🙂

Мне нужно было добавить строку ПЛЮС значение ячейки. Поскольку в файле справки не было этого в качестве примера, я решил опубликовать это для вас и всех будущих меня.

Кстати, я не могу вспомнить все приемы. Я часто использую СВОЙ СОБСТВЕННЫЙ сайт, чтобы посмотреть, как я решил проблему ранее. Например, на последнем пятничном конкурсе я зашел на этот сайт и искал термины, которые запомнил, пока не нашел то, что искал: как преобразовать существующий набор данных Excel в -формат сводной таблицы

Затем я просмотрел совет/рекомендацию по Excel и применил технику. На самом деле, я публикую это, чтобы в СЛЕДУЮЩИЙ раз, когда я попытаюсь использовать функцию СУММЕСЛИМН, и она не работает, я знал, что мне нужно добавить «&» перед значением ячейки 🙂

Надеюсь, это сэкономит вам несколько минут разочарования, когда дело доходит до правильного синтаксиса для функции Excel.

Какую функцию вы потратили много времени, пытаясь понять, а потом это была просто проблема с синтаксисом? Дайте мне знать в комментариях ниже.

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

У меня есть приведенная ниже формула СУММЕСЛИМН, и, похоже, она работает неправильно, любая помощь будет очень признательна.

Формула ниже

Из рисунка видно, что результат в ячейке R4 должен быть равен 4, но возвращается 0

Мы будем очень признательны за любую помощь.

2 ответа 2

SumIfs работает с диапазоном_суммы, диапазоном_критериев, критериями.

Вы указали сумму_диапазона, критерия_диапазона, критерия_ячейки и критерия.

Попробуйте =СУММЕСЛИ(C4:N4,C4:N4," , или, так как это только один критерий =СУММЕСЛИ(C4:N4,"

Редактировать: только что заметил - первый критерий должен быть =СУММ(ЕСЛИ(C4

Спасибо за помощь =SUM(IF(C4 работает очень хорошо. Мне было интересно, могу ли я задать вам еще одну задачу. В ячейке S4 я хотел бы суммировать или суммировать, если C4 находится между 11 и 19, а F4 между 2 и 20, и I4 между 2 и 20 и L4 между 2 и 20. Возможно ли это? Еще раз спасибо за всю помощь.

Навскидку это сработает: =СУММ(ЕСЛИ(И(C4>=11,C4 =2,F4 =2,I4 =2,L4 . Мне кажется, должно быть лучше однако.

К сожалению, =SUM(IF(AND(C4>=11,C4=2,F4=2,I4=2,‌L4, спасибо за попытку. Большое спасибо.

Не знаю, в чем прискорбие. Разве это не сработало, как вы планировали? На самом деле - я просто скопировал его и вставил, и в формулу были помещены какие-то странные столбцы I с невидимыми символами, поэтому выдавалось сообщение формула содержит ошибки. Каждая часть формулы должна быть в формате IF(AND(C4>=11,C4 - если между 11 и 19 включительно, то используйте значение C4 в сумме, в противном случае используйте 0. Затем просто просуммируйте результаты.

Это сработает - AND(C4>=12, C4 вернет TRUE (1) или FALSE (0), а затем умножит на значение в C4, так что либо C4*1, либо C4*0. :)

Использование СУММЕСЛИМН означает, что вы хотите добавить значения в C4:N4, только если все условия ИСТИННЫ. Если это правильно, используйте эту формулу.

'СУММЕСЛИМН' предназначен для операций с диапазонами, в которых добавляется один столбец, если выполняются все условия в других столбцах, см. Метод WorksheetFunction.SumIfs. Но в вашем случае вы тестируете условия в строке, и если они выполняются, вы хотите добавить значения в строку.

Эта формула не работает, если одно из значений равно FALSE. Если F4 равно 2, формула возвращает 0, а не 3 (я думаю, что ОП хочет, чтобы это работало). Столбцы/строки не имеют значения для СУММЕСЛИМН, как указано на странице справки: Каждый диапазон критериев не обязательно должен иметь тот же размер и форму, что и диапазон_суммы. Фактически добавляемые ячейки определяются путем использования верхней левой ячейки в этом диапазоне критериев в качестве начальной ячейки, а затем включения ячеек, соответствующих по размеру и форме диапазону суммы. (что само по себе сбивает с толку).

Прочитал, просто не понял. СУММЕСЛИМН добавит значения в C4:N4, которые соответствуют всем критериям для этой ячейки, или, если вы предпочитаете, каждая ячейка в сумме_диапазон суммируется, только если все соответствующие указанные критерии верны для этой ячейки.

Зарегистрированный пользователь Регистрация 13.01.2004 Сообщений 42

СУММЕСЛИ работает неправильно

Здравствуйте,
У меня есть простая формула суммирования, и я заметил, что она не складывается должным образом. В некоторых случаях он суммирует только первый найденный критерий.

Например, в K16 есть 1. F16 - F66 имеет 1 несколько раз и , 2s, 3s и т.д.
Но формула только суммирует 1-й 1. Я проверил, отформатированы ли столбцы от H до I как валюта, и поэтому они не в тексте.
есть идеи?

Администратор Дата присоединения 21 марта 2004 г. Местоположение Бейкерсфилд, Калифорния MS-Off Ver 2010, 2016, Office 365 Сообщений 33 492

Если сумма суммируется неправильно, это проблема в столбце F. Убедитесь, что ЭТОТ столбец имеет тот же тип данных, что и K16.

Нажмите "ДОПОЛНИТЕЛЬНО" и используйте значок скрепки, чтобы опубликовать свою рабочую книгу, показывающую некорректные данные, чтобы мы могли проверить ее напрямую.

_________________
Microsoft MVP 2010 — Excel
Посетите: файлы и макросы Excel Джерри Бокера

Если вам оказали хорошую помощь, используйте значок ниже, чтобы оставить отзыв о репутации, это приветствуется.
Всегда помещайте свой код между тегами кода. [CODE] ваш код здесь [/CODE]

"Никто из нас не так хорош, как все мы" – Рэй Крок.
"На самом деле я ** ** ракетчик. – JB (маленькие считаются!)

Forum Expert Регистрация Дата 03-05-2008 Местоположение Хьюстон, Техас MS-Off Версия 2013 Сообщений 2191

Попробуйте =Sumproduct(($F$16:$F$66=K16)*($H$16:$I$66)).
Также обратите внимание, что если числа получены из внешнего источника, простое форматирование их как валюты не сделает их валютой. Попробуйте ввести 0 в ячейку, скопировать ее, выбрать H16:I66 и вставить специальный-->Добавить. Это приведет к тому, что эти значения будут числовыми.

Модератор форума Регистрация 14.01.2006 Местоположение Англия MS-Off Ver 2016 Сообщений 14 676

СУММЕСЛИ работает только на основе 1:1, что означает, что диапазон суммы должен быть того же размера, что и диапазон критериев, что означает, что ваша формула будет суммировать только столбец H, попробуйте использовать СУММПРОИЗВ следующим образом

Зарегистрированный пользователь Регистрация 13.01.2004 Сообщений 42

Спасибо, формула суммового произведения сработала.
не подскажете, почему в данном случае не сработал сумиф? Мне не нужно было использовать предложенное вами значение вставки специального значения.

Forum Expert Регистрация Дата 03-05-2008 Местоположение Хьюстон, Техас MS-Off Версия 2013 Сообщений 2191

Джей Би, дело не только в этом. С SUMIF он не повторяет совпадения из столбца F. Другими словами, он будет считывать 51 значение и суммировать 51 значение из H, игнорируя значения из I. См. простую прикрепленную рабочую книгу. Sumproduct действительно повторно использует значения, используя совпадения из H дважды и, таким образом, давая правильный ответ.

*Редактировать: немного медленно. DLL опередил меня.

Привет, проблема связана с аргументом sum_range. Ваш аргумент range представляет собой ссылку на один столбец, но ваш sum_range представляет собой ссылку на два столбца. Хотя range и sum_range не обязательно должны иметь одинаковую форму и размер, в этом случае sumif суммирует только первый столбец sum_range.

Чтобы захватить оба столбца, вам нужно сделать что-то вроде этого:

DLL, это из файлов справки 2010 года:

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

Forum Expert Регистрация Дата 03-05-2008 Местоположение Хьюстон, Техас MS-Off Версия 2013 Сообщений 2191

Нет, это не так. Я все время забываю об этой функции, но она появилась примерно в 2007 году, и я думаю, что кто-то здесь уже указывал, что она и в 2003 году. Однако это не меняет сути того, что если диапазон критериев составляет только один столбец, диапазон суммы будет таким же, независимо от того, как вы его установите.

Модератор форума Регистрация 14.01.2006 Местоположение Англия MS-Off Ver 2016 Сообщений 14 676

СУММЕСЛИ совсем не изменился. Я думаю, что формулировка файлов справки немного вводит в заблуждение. Когда он говорит

"Аргумент sum_range не обязательно должен иметь тот же размер и форму, что и аргумент диапазона"

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

В Excel 2007 СРЗНАЧЕСЛИ работает так же, но в функциях СУММЕСЛИМН и СРЗНАЧЕСЛИМН Excel заставляет вас явно определять все диапазоны одинакового размера и формы, иначе вы получите ошибку

Им действительно удалось наговорить много слов о том, что СУММЕСЛИ будет суммировать только sum_range той же формы и размера, что и range.< /p>

Зарегистрированный пользователь Регистрация 09.12.2018 Расположение Чикаго, Иллинойс MS-Off Ver office 365 Сообщений 7

У меня та же проблема, но с преобразованием текста в продукт, работая с Office 365, у меня должно быть кумулятивное значение для моих категорий 403, но Excel будет считать только 284. Как мне получить все 403 значения по категориям? Я использую sumif и в формуле: sumif(A:A,"категория",B:B). Это должно дать мне значение, скажем, джипа, которое равно 33, но Excel выдает с приведенной выше формулой 18. Почему отсутствуют остальные 15 совокупных значений?

Модератор форума Регистрация 11.10.2011 Место проведения Афины(Родина Демократии!). Греция MS-Off Ver Excel 1997!&2003&2007&2010 Сообщений 13,744

lloyd
К сожалению, ваше сообщение не соответствует правилу 4 нашего форума ПРАВИЛА . Не размещайте вопрос в ветке другого участника — начните свою собственную ветку.

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

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

-- Помните, что сказать "спасибо" можно всего за одну-две секунды. Нажмите на маленькую звездочку * ниже , чтобы дать репутацию, если вы считаете, что ответ заслуживает этого.

СУММЕСЛИМН – это функция суммирования ячеек, соответствующих нескольким критериям. СУММЕСЛИМН можно использовать для суммирования значений, когда соответствующие ячейки соответствуют критериям, основанным на датах, числах и тексте. СУММЕСЛИМН поддерживает логические операторы (>, ,=) и подстановочные знаки (*,?) для частичного совпадения.

sum_range – суммируемый диапазон.
range1 — первый диапазон для оценки.
criteria1 — критерии для диапазона 1.
range2 – [необязательно] второй диапазон для оценки.
criteria2 – [необязательно] критерии для диапазона2.

Функция СУММЕСЛИМН суммирует ячейки в диапазоне с использованием предоставленных критериев. В отличие от функции СУММЕСЛИ, функция СУММЕСЛИМН может применять более одного набора критериев с более чем одним диапазоном. Первый диапазон – это диапазон, который необходимо суммировать. Критерии представлены парами (диапазон/критерии), и требуется только первая пара. Чтобы применить дополнительные критерии, укажите дополнительную пару диапазон/критерий. Допускается использование до 127 пар "диапазон/критерий".

Критерии могут включать логические операторы (>, ,=) и подстановочные знаки (*,?) для частичного совпадения. Критерии также могут быть основаны на значении из другой ячейки, как описано ниже.

СУММЕСЛИМН входит в группу из восьми функций Excel, которые разбивают логические критерии на две части (диапазон + критерии). В результате синтаксис, используемый для построения критериев, отличается, и СУММЕСЛИМН требуется диапазон ячеек для аргументов диапазона, вы не можете использовать массив.

Если вам нужно манипулировать значениями, которые появляются в аргументе range (т. е. извлекать год из дат для использования в критериях), см. функции СУММПРОИЗВ и/или ФИЛЬТР.

Примеры

Для одного условия общая схема СУММЕСЛИМН такова:

Когда ячейки в range1 соответствуют условию1, соответствующие ячейки в sum_range суммируются. Для двух условий шаблон таков:

Критерии уведомления указаны в парах диапазон/критерий. Когда ячейки в диапазоне1 соответствуют критерию1, и ячейки в диапазоне2 соответствуют критерию2, соответственно ячейки в sum_range суммируются. Тот же шаблон расширен для обработки большего количества условий.

Пример рабочего листа

На показанном листе есть две формулы СУММЕСЛИМН. В первом примере (I5) функция СУММЕСЛИМН возвращает сумму значений в столбце F, где цвет столбца C — «красный». Во втором примере (I6) СУММЕСЛИМН суммирует значения в столбце F, когда цвет "красный" и штат Техас (TX):

Обратите внимание, что знак равенства (=) не требуется при построении критерия "равно". Также обратите внимание, что СУММЕСЛИМН не чувствителен к регистру; вы можете использовать «красный» или «красный», а также «TX» или «tx».

Примечания

Функция СУММЕСЛИМН может суммировать диапазоны на основе нескольких критериев. В этой задаче мы настраиваем СУММЕСЛИМН для суммирования сумм в именованном диапазоне по номеру недели, используя два критерия: цвет = значение в столбце G неделя = значение.

Функция СУММЕСЛИ поддерживает логические операторы Excel (например, "=",">",">=" и т. д.), поэтому вы можете использовать их по своему усмотрению в своих критериях. В этом случае мы хотим сопоставить суммы больше 1000, а "диапазон критериев" равен

Функция СУММЕСЛИМН предназначена для суммирования значений в диапазоне на основе одного или нескольких критериев. В этом случае нам нужны три критерия: Имя клиента = клиент в столбце F Дата >= первого числа месяца (от даты в строке 4) Дата

В обеих формулах используются встроенные функции для вычисления промежуточного итога, но синтаксис, используемый СУММЕСЛИ и СУММЕСЛИМН, немного отличается: СУММЕСЛИ(диапазон,критерий,сумма_диапазон) СУММЕСЛИМН(сумма_диапазон,диапазон,критерий) Обратите внимание на это в обоих случаях.

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

Время в Excel — это числа, и их можно суммировать, как и другие числовые значения. В этом примере F4:G7 — это сводная таблица, показывающая общее время регистрации в каждом из трех состояний: «Ожидание», «Работа» и «Автономный режим». Эти значения.

Первым аргументом для СУММЕСЛИ всегда является диапазон для суммирования ("диапазон_суммы"), а критерии предоставляются в виде одной или нескольких пар "диапазон/критерий". В этом примере диапазон сумм – это именованный диапазон с именем "сумма" (E3:E2931) и.

Функция СУММЕСЛИ поддерживает подстановочные знаки. Звездочка (*) означает «один или несколько символов», а вопросительный знак (?) означает «любой один символ». Эти подстановочные знаки позволяют создавать такие критерии, как "начинается с", "заканчивается на".

Эта формула демонстрирует очень простую концепцию инвентаризации, в которой текущий инвентарь представляет собой просто результат всех входящих запасов за вычетом всех исходящих запасов. В примере цвета рассматриваются как уникальные идентификаторы элементов –.

Первый диапазон (D4:D11) — это ячейки для суммирования, называемые "диапазоном суммирования". Критерии поставляются парами. (диапазон/критерий). Первая пара критериев (B4:B11 / "синий"). Это означает, что ячейки в B4:B11 должны иметь значение "синий".

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