В этом руководстве подробно показано, как использовать функции СУММЕСЛИ и СУММЕСЛИМН в Google Таблицах с формулами и примерами.
СУММЕСЛИ - одна из математических функций в Google Таблицах, которая используется для условного суммирования ячеек. По сути, функция СУММЕСЛИ ищет определенное условие в диапазоне ячеек, а затем складывает значения, соответствующие данному условию.
Например, у вас есть список расходов в таблицах Google, и вы хотите просуммировать только те расходы, которые превышают определенное максимальное значение. Или у вас есть список позиций заказа и их соответствующих сумм, и вы хотите знать только общую сумму заказа по конкретному пункту. Вот здесь и пригодится функция СУММЕСЛИ.
СУММЕСЛИ можно использовать для суммирования значений на основе числового условия, текстового условия, условия даты, подстановочных знаков, а также на основе пустых и непустых ячеек. В Google Таблицах есть две функции для суммирования значений на основе критериев: СУММЕСЛИ и СУММЕСЛИМН. Функция СУММЕСЛИ складывает числа на основе одного условия, а СУММЕСЛИМН суммирует числа на основе нескольких условий.
В этом руководстве мы объясним, как использовать функции СУММЕСЛИ и СУММЕСЛИМН в Google Таблицах для суммирования чисел, соответствующих определенным условиям.
Функция СУММЕСЛИ в Google Таблицах - синтаксис и аргументы
Функция СУММЕСЛИ - это просто комбинация функций СУММ и ЕСЛИ. Функция ЕСЛИ просматривает диапазон ячеек для данного условия, а затем функция СУММ суммирует числа, соответствующие ячейкам, удовлетворяющим условию.
Синтаксис функции СУММЕСЛИ:
Синтаксис функции СУММЕСЛИ в Google Таблицах следующий:
= СУММЕСЛИ (диапазон; критерий; [диапазон_суммы])Аргументы:
диапазон - Диапазон ячеек, в котором мы ищем ячейки, соответствующие критериям.
критерии - Критерии, определяющие, какие ячейки необходимо добавить. Вы можете основывать критерий на числе, текстовой строке, дате, ссылке на ячейку, выражении, логическом операторе, подстановочном знаке, а также на других функциях.
диапазон_суммы - Этот аргумент не является обязательным. Это диапазон данных со значениями для суммирования, если соответствующая запись диапазона соответствует условию. Если вы не укажете этот аргумент, вместо этого будет суммироваться «диапазон».
Теперь давайте посмотрим, как использовать функцию СУММЕСЛИ для суммирования значений с разными критериями.
Функция СУММЕСЛИ с числовыми критериями
Вы можете суммировать числа, соответствующие определенным критериям в диапазоне ячеек, используя один из следующих операторов сравнения для создания критериев.
- больше чем (>)
- меньше чем (<)
- больше или равно (> =)
- меньше или равно (<=)
- равно (=)
- не равно ()
Предположим, у вас есть следующая электронная таблица, и вас интересуют общие продажи, которые составляют 1000 или выше.
Вот как можно ввести функцию СУММЕСЛИ:
Сначала выберите ячейку, в которой должны отображаться выходные данные суммы (D3). Чтобы суммировать числа в B2: B12, которые больше или равны 1000, введите эту формулу и нажмите «Enter»:
= СУММЕСЛИ (B2: B12; "> = 1000"; B2: B12)В этом примере формулы аргументы диапазона и диапазона_суммы (B2: B12) совпадают, поскольку показатели продаж и критерии применяются к одному и тому же диапазону. И мы ввели число перед оператором сравнения и заключили его в кавычки, потому что критерии всегда должны быть заключены в двойные кавычки, за исключением ссылки на ячейку.
Формула искала числа, которые больше или равны 1000, а затем складывала все совпавшие значения и отображала результат в ячейке D3.
Поскольку аргументы range и sum_range одинаковы, вы можете достичь того же результата без аргументов sum_range в формуле, например:
= СУММЕСЛИ (B2: B12; "> = 1000")
Или вы можете указать ссылку на ячейку (D2), которая содержит номер вместо числового критерия, и присоединить оператор сравнения со ссылкой на эту ячейку в аргументе критерия:
= СУММЕСЛИ (B2: B12, "> =" & D2)Как видите, оператор сравнения по-прежнему заключен в двойные кавычки, а оператор и ссылка на ячейку объединены амперсандом (&). И вам не нужно заключать ссылку на ячейку в кавычки.
Примечание: При обращении к ячейке, содержащей критерии, убедитесь, что в значении ячейки не осталось пробелов в начале или в конце. Если у вашего значения есть ненужное пространство перед или после значения в указанной ячейке, то в результате формула вернет «0».
Таким же образом можно использовать другие логические операторы для создания условий в аргументе критериев. Например, чтобы суммировать значения меньше 500:
= СУММЕСЛИ (B2: B12; «<500»)
Сумма, если числа равны
Если вы хотите добавить числа, которые равны определенному числу, вы можете ввести только число или ввести число со знаком равенства в аргументе критерия.
Например, чтобы суммировать соответствующие суммы продаж (столбец B) для количеств (столбец C), значения которых равны 20, попробуйте любую из этих формул:
= СУММЕСЛИ (C2: C12; "= 20"; B2: B12)
= СУММЕСЛИ (C2: C12; «20»; B2: B12)
= СУММЕСЛИ (C2: C12; E2; B2: B12)
Чтобы суммировать числа в столбце B с количеством, не равным 20 в столбце C, попробуйте следующую формулу:
= СУММЕСЛИ (C2: C12; «20»; B2: B12)
Функция СУММЕСЛИ с текстовыми критериями
Если вы хотите сложить числа в диапазоне ячеек (столбце или строке), соответствующем ячейкам с определенным текстом, вы можете просто включить этот текст или ячейку, содержащую текст, в аргумент критерия вашей формулы СУММЕСЛИ. Обратите внимание, что текстовая строка всегда должна быть заключена в двойные кавычки ("").
Например, если вам нужен общий объем продаж в «Западном» регионе, вы можете использовать следующую формулу:
= СУММЕСЛИ (C2: C13; «Запад»; B2: B13)В этой формуле функция СУММЕСЛИ ищет значение «Запад» в диапазоне ячеек C2: C13 и складывает соответствующее значение продаж в столбце B. Затем результат отображает результат в ячейке E3.
Вы также можете ссылаться на ячейку, содержащую текст, вместо использования текста в аргументе критерия:
= СУММЕСЛИ (C2: C12; E2; B2: B12)
Теперь давайте получим общий доход по всем регионам, кроме "Запада". Для этого мы будем использовать не равный оператору () в формуле:
= СУММЕСЛИ (C2: C12; "" & E2; B2: B12)
СУММЕСЛИ с подстановочными знаками
В указанном выше методе функция СУММЕСЛИ с текстовыми критериями проверяет диапазон на соответствие точно указанному тексту. Затем он суммирует числа, относящиеся к точному тексту, и игнорирует все другие числа, включая частично совпадающую текстовую строку. Чтобы суммировать числа с частично совпадающими текстовыми строками, вы должны адаптировать один из следующих подстановочных знаков в ваших критериях:
?(вопросительный знак) используется для соответствия любому одиночному символу в любом месте текстовой строки.*(звездочка) используется для поиска совпадающих слов вместе с любой последовательностью символов.~(тильда) используется для сопоставления текстов с вопросительным знаком (?) или символом звездочки (*).
Вот пример таблицы для продуктов и их количества, чтобы суммировать числа с подстановочными знаками:
Звездочка (*) Подстановочный знак
Например, если вы хотите просуммировать количество всех продуктов Apple, используйте эту формулу:
= СУММЕСЛИ (A2: A14; «Яблоко *»; B2: B14)Эта формула СУММЕСЛИ находит все продукты со словом «Apple» в начале и любым количеством символов после него (обозначается «*»). Как только совпадение найдено, он подводит итоги Количество числа, соответствующие совпадающим текстовым строкам.
Также можно использовать несколько подстановочных знаков в критериях. И вы также можете вводить подстановочные знаки со ссылками на ячейки вместо прямого текста.
Для этого подстановочные знаки должны быть заключены в двойные кавычки («») и объединены со ссылкой (ями) на ячейку:
= СУММЕСЛИ (A2: A14; «*» & D2 & «*»; B2: B14)Эта формула суммирует количества всех продуктов, в которых есть слово «Redmi», независимо от того, где это слово находится в строке.
Знак вопроса (?) Подстановочный знак
Вы можете использовать подстановочный знак вопросительного знака (?) Для сопоставления текстовых строк с любыми одиночными символами.
Например, если вы хотите найти количество всех вариантов Xiaomi Redmi 9, вы можете использовать эту формулу:
= СУММЕСЛИ (A2: A14; «Xiaomi Redmi 9?»; B2: B14)Приведенная выше формула ищет текстовые строки со словом «Xiaomi Redmi 9», за которым следуют любые отдельные символы, и суммирует соответствующие Количество числа.
Тильда (~) Подстановочный знак
Если вы хотите сопоставить фактический вопросительный знак (?) Или звездочку (*), вставьте символ тильды (~) перед подстановочным знаком в части условия формулы.
Чтобы добавить количества в столбце B с соответствующей строкой со знаком звездочки в конце, введите следующую формулу:
= СУММЕСЛИ (A2: A14; «Samsung Galaxy V ~ *»; B2: B14)
Чтобы добавить количества в столбце B, у которых есть вопросительный знак (?) В столбце A той же строки, попробуйте следующую формулу:
= СУММЕСЛИ (A2: A14; «~?»; B2: B14)
Функция СУММЕСЛИ с критериями даты
Функция СУММЕСЛИ может также помочь вам условно суммировать значения на основе критериев даты - например, числа, соответствующие определенной дате, или до даты, или после даты. Вы также можете использовать любой из операторов сравнения со значением даты, чтобы создать критерии даты для суммирования чисел.
Дата должна быть введена в формате даты, поддерживаемом таблицами Google, или в качестве ссылки на ячейку, которая содержит дату, или с использованием функции даты, такой как ДАТА () или СЕГОДНЯ ().
Мы будем использовать этот пример электронной таблицы, чтобы показать вам, как работает функция СУММЕСЛИ с критериями даты:
Предположим, вы хотите просуммировать суммы продаж, которые произошли 29 ноября 2019 г. или ранее (<=) в приведенном выше наборе данных, вы можете добавить эти цифры продаж с помощью функции СУММЕСЛИ одним из следующих способов:
= СУММЕСЛИ (C2: C13; «<= 29 ноября 2019 г.»; B2: B13)Приведенная выше формула проверяет каждую ячейку от C2 до C13 и соответствует только тем ячейкам, которые содержат даты до 29 ноября 2019 г. или ранее (29/11/2019). Затем суммирует сумму продаж, соответствующую совпадающим ячейкам из диапазона ячеек B2: B13, и отображает результат в ячейках E3.
Дата может быть указана в формуле в любом формате, который распознается Google Таблицами, например «29 ноября 2019 г.», «29 ноября 2019 г.» или «29.11.2019» и т. Д. Запомните значение даты, и оператор должен всегда заключать в двойные кавычки.
Вы также можете использовать функцию DATE () в критериях вместо прямого значения даты:
= СУММЕСЛИ (C2: C13; "<=" & ДАТА (2019,11,29); B2: B13)
Или вы можете использовать ссылку на ячейку вместо даты в части формулы формулы:
= СУММЕСЛИ (C2: C13; "<=" & E2; B2: B13)
Если вы хотите сложить суммы продаж на основе сегодняшней даты, вы можете использовать функцию СЕГОДНЯ () в аргументе критерия.
Например, чтобы суммировать все суммы продаж на сегодняшний день, используйте следующую формулу:
= СУММЕСЛИ (C2: C13; СЕГОДНЯ (); B2: B13)
Функция СУММЕСЛИ с пустыми или непустыми ячейками
Иногда вам может потребоваться суммировать числа в диапазоне ячеек с пустыми или непустыми ячейками в одной строке. В таких случаях вы можете использовать функцию СУММЕСЛИ для суммирования значений на основе критериев, в которых ячейки пусты или нет.
Сумма, если пусто
В Google Таблицах есть два критерия поиска пустых ячеек: «» или «=».
Например, если вы хотите просуммировать все суммы продаж, которые содержат строки нулевой длины (визуально выглядят пустыми) в столбце C, используйте в формуле двойные кавычки без пробелов между ними:
= СУММЕСЛИ (C2: C13; ""; B2: B13)
Чтобы суммировать всю сумму продаж в столбце B с полными пустыми ячейками в столбце C, включите «=» в качестве критерия:
= СУММЕСЛИ (C2: C13; "="; B2: B13)
Сумма, если не пусто:
Если вы хотите суммировать ячейки, содержащие любое значение (не пустое), вы можете использовать «» в качестве критерия в формуле:
Например, чтобы получить общую сумму продаж с любыми датами, используйте эту формулу:
= СУММЕСЛИ (C2: C13; ""; B2: B13)
СУММЕСЛИ на основе нескольких критериев с логикой ИЛИ
Как мы уже видели, функция СУММЕСЛИ предназначена для суммирования чисел на основе только одного критерия, но с помощью функции СУММЕСЛИ в Google Таблицах можно суммировать значения на основе нескольких критериев. Это можно сделать, объединив более одной функции СУММЕСЛИ в одной формуле с логикой ИЛИ.
Например, если вы хотите просуммировать сумму продаж в регионе «Запад» или «Юг» (логика ИЛИ) в указанном диапазоне (B2: B13), используйте эту формулу:
= СУММЕСЛИ (C2: C13; «Запад»; B2: B13) + СУММЕСЛИ (C2: C13; «Юг»; B2: B13)Эта формула суммирует ячейки, если хотя бы одно из условий ИСТИНА. Следовательно, это известно как «логика ИЛИ». Он также будет суммировать значения при соблюдении всех условий.
Первая часть формулы проверяет диапазон C2: C13 на наличие текста «Запад» и суммирует значения в диапазоне B2: B13, когда соответствие найдено. Секундная часть проверяет наличие текстового значения «Юг» в том же диапазоне C2: C13, а затем суммирует значения с совпадающим текстом в том же диапазоне сумм B2: B13. Затем обе суммы складываются и отображаются в ячейке E3.
В случаях, когда удовлетворяется только один критерий, будет возвращено только это значение суммы.
Вы также можете использовать несколько критериев вместо одного или двух. А если вы используете несколько критериев, лучше использовать ссылку на ячейку в качестве критерия вместо того, чтобы записывать прямое значение в формуле.
= СУММЕСЛИ (C2: C13, E2; B2: B13) + СУММЕСЛИ (C2: C13, E3, B2: B13) + СУММЕСЛИ (C2: C13, E4, B2: B13)
СУММЕСЛИ с логикой ИЛИ добавляет значения, когда выполняется хотя бы один из указанных критериев. Но если вы хотите суммировать значения только тогда, когда все указанные условия выполняются, вы должны использовать его новую функцию-аналог SUMIFS ().
Функция СУММЕСЛИМН в Google Таблицах (несколько критериев)
Когда вы используете функцию СУММЕСЛИ для суммирования значений на основе нескольких критериев, формула может стать слишком длинной и сложной, и вы подвержены ошибкам. Кроме того, СУММЕСЛИ позволяет суммировать значения только в одном диапазоне и когда любое из условий истинно. Здесь на помощь приходит функция СУММЕСЛИМН.
Функция СУММЕСЛИМН помогает суммировать значения на основе нескольких критериев соответствия в одном или нескольких диапазонах. И он работает по логике И, что означает, что он может суммировать значения только при соблюдении всех данных условий. Даже если одно условие ложно, в результате будет возвращено «0».
Синтаксис и аргументы функции СУММЕСЛИМН
Синтаксис функции СУММЕСЛИМН следующий:
= СУММЕСЛИМН (диапазон_суммы, диапазон_критериев1, критерий1, [диапазон_критериев2, ...], [критерий2, ...])Где,
- диапазон_суммы - Диапазон ячеек, содержащий значения, которые вы хотите суммировать при соблюдении всех условий.
- диапазон_критериев1 - Это диапазон ячеек, в котором вы проверяете критерии1.
- критерии1 - Это условие, которое вы должны проверить по критерию диапазон_критерия1.
- criteria_range2, критерий2,…- Дополнительные диапазоны и критерии для оценки. И вы можете добавить в формулу дополнительные диапазоны и условия.
Мы будем использовать набор данных на следующем снимке экрана, чтобы продемонстрировать, как функция СУММЕСЛИМН работает с различными критериями.
СУММЕСЛИМН с текстовыми условиями
Вы можете суммировать значения на основе двух разных текстовых критериев в разных диапазонах. Например, предположим, вы хотите узнать общую сумму продаж доставленного товара для палатки. Для этого используйте эту формулу:
= СУММЕСЛИМН (D2: D13; A2: A13; «Палатка»; C2: C13; «Доставлено»)В этой формуле у нас есть два критерия: «Палатка» и «Доставлено». Функция СУММЕСЛИМН проверяет элемент «Палатка» (критерий1) в диапазоне A2: A13 (диапазон_критерия1) и проверяет статус «Доставлено» (критерий2) в диапазоне C2: C13 (диапазон_критерия2). Когда оба условия соблюдены, суммируется соответствующее значение в диапазоне ячеек D2: D13 (диапазон_суммирования).
СУММЕСЛИМН с числовыми критериями и логическими операторами
Вы можете использовать условные операторы для создания условий с числами для функции СУММЕСЛИМН.
Чтобы найти общий объем продаж более 5 партий любого товара в штате Калифорния (CA), используйте эту формулу:
= СУММЕСЛИМН (E2: E13; D2: D13; "> 5"; B2: B13; "CA")Эта формула имеет два условия: «> 5» и «CA».
Эта формула проверяет количество (Qty) больше 5 в диапазоне D2: D13 и проверяет состояние «CA» в диапазоне B2: B13. И когда оба условия выполнены (то есть они находятся в одной строке), сумма суммируется в E2: E13.
СУММЕСЛИМН с критериями даты
Функция СУММЕСЛИМН также позволяет вам проверять несколько условий в одном и том же диапазоне, а также в разных диапазонах.
Предположим, вы хотите проверить общую сумму продаж доставленных товаров после 31.05.2021 и до даты 06.10.2021, а затем используйте эту формулу:
= СУММЕСЛИМН (E2: E13, D2: D13, ">" & G1, D2: D13, "<" & G2, C2: C13, G3)Вышеупомянутая формула имеет три условия: 31/5/2021, 10/5/2021 и Delivered. Вместо использования прямых значений даты и текста мы обратились к ячейкам, содержащим эти критерии.
Формула проверяет даты после 31.05.2021 (G1) и даты до 06.10.2021 (G2) в том же диапазоне D2: D13 и проверяет статус «Доставлено» между этими двумя датами. Затем суммирует соответствующую сумму в диапазоне E2: E13.
СУММЕСЛИМН с пустыми и непустыми ячейками
Иногда вам может потребоваться найти сумму значений, когда соответствующая ячейка пуста или нет. Для этого вы можете использовать один из трех критериев, которые мы обсуждали ранее: «=», «» и «».
Например, если вы хотите суммировать только количество предметов типа «Палатка», для которых дата доставки еще не подтверждена (пустые ячейки), вы можете использовать критерий «=»:
= СУММЕСЛИМН (D2: D13; A2: A13; «Палатка»; C2: C13; «=»)Формула ищет элемент «Палатка» (критерий1) в столбце A с соответствующими пустыми ячейками (критерии2) в столбце C, а затем суммирует соответствующую сумму в столбце D. Знак «=» представляет собой полностью пустую ячейку.
Чтобы найти общее количество элементов «Палатка», для которых была подтверждена дата доставки (не пустые ячейки), используйте «» в качестве критерия:
= СУММЕСЛИМН (D2: D13; A2: A13; «Палатка»; C2: C13; «»)Мы просто заменили «=» на «» в этой формуле. Он находит сумму элементов палатки с непустыми ячейками в столбце C.
СУММЕСЛИМН с логикой ИЛИ
Поскольку функция СУММЕСЛИМН работает по логике И, она суммирует только при соблюдении всех условий. Но что, если вы хотите суммировать стоимость на основе нескольких критериев, когда выполняется любой из критериев. Уловка состоит в том, чтобы использовать несколько функций СУММЕСЛИМН.
Например, если вы хотите сложить сумму продаж для «Велобарона» ИЛИ «Рюкзак», когда их статус «Заказан», попробуйте следующую формулу:
= СУММЕСЛИМН (D2: D13, A2: A13, «Крепление для велосипеда», C2: C13, «Заказано») + СУММЕСЛИМН (D2: D13, A2: A13, «Рюкзак», C2: C13, «Заказано»)Первая функция СУММЕСЛИМН проверяет два критерия «Крепление для велосипедов» и «Заказано» и суммирует значения суммы в столбце D. Затем вторая функция СУММЕСЛИМН проверяет два критерия «Рюкзак» и «Заказано» и суммирует значения суммы в столбце D. И затем , обе суммы складываются и отображаются на F3. Проще говоря, эта формула суммирует, когда заказывается либо «Крепление для велосипеда», либо «Рюкзак».
Это все, что вам нужно знать о функциях СУММЕСЛИ и СУММЕСЛИМН в Google Таблицах.