Формула суммы ячеек в Excel

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

В примере показано, ячейка G6 содержит эту формулу: 

Эта формула суммирует суммы в столбце D, когда значение в столбце C содержит "*".

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

Эти шаблоны позволяют создавать такие критерии, как "начинается с", "кончается на", "содержит 3 символов" и так далее.

Потому как звездочки и вопросительные знаки, сами по себе являются подстановочными знаками, если вы хотите искать эти символы в частности, вы должны защитить их с тильдой (~). Тильда причины Excel следует рассматривать следующий символ буквально.

В этом случае мы используем "~*", чтобы соответствовать символу звездочки, но он окружен звездочками по бокам, для того чтобы соответствовать звездочкам в любом месте ячейки. Если вы просто хотите, чтобы соответствовало звездочкам на конце ячейки, используйте: "*~**" по критериям.

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

Чтобы посчитать сумму, если ячейки содержат X и Y (т. е. содержат "кошка" и "крыса", в одной ячейке) можно использовать функцию СУММЕСЛИМН. 

В показанном примере, формула в F5: 

Функция СУММЕСЛИМН основана на логике и такое поведение является автоматическим. Нам просто нужно поставить два диапазона/критерии пары, работающие на одном диапазоне (В5:В9).

Для обоих критериев (содержит "крыса", содержится "кошка") мы используем звездочку, которая является подстановочным, что означает "один или несколько символов". Мы ставим звездочку в начале и в конце, чтобы позволить формуле проверить, чтобы соответствовало "кошка" и "крыса" везде, где они появляются в ячейке.

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

Обратите внимание, что СУММЕСЛИМН не чувствителен к регистру.

Функция СУММЕСЛИМН основана на логике и такое поведение является автоматическим. Нам просто нужно поставить два диапазона/критерии пары, работающие на одном диапазоне (В5:В9).

Для обоих критериев (содержит "крыса", содержится "кошка") мы используем звездочку, которая является подстановочным, что означает "один или несколько символов". Мы ставим звездочку в начале и в конце, чтобы позволить формуле проверить, чтобы соответствовало "кошка" и "крыса" везде, где они появляются в ячейке.

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

Обратите внимание, что СУММЕСЛИМН не чувствителен к регистру.

Эта формула основана на формуле, что находит текст внутри ячейки:

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

Далее, мы добавляем эти массивы вместе (с +), которая создает новый единый массив чисел. Каждое число в этом массиве является результатом добавления истинных и ложных значений в исходных двух массивах вместе. В показанном примере, массив выглядит так:

Нам нужно добавить эти цифры, но мы не хотим, чтобы удвоился счет. Поэтому мы должны убедиться, что любое значение больше нуля, всего лишь раз пересчитать. Чтобы сделать это, мы превращаем все значения Истина или Ложь, проверяя массив с ">0". Это рассчитывает Истина или Ложь:

Которые мы затем преобразовываем в 1/0 с помощью двойного отрицания (--):

Параметр чувствителен к регистру

Функция поиска не учитывает регистр. Если вам нужен чувствительный вариант, замените поиск на найти.

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

В примере показано, ячейка G4 содержит эту формулу:

Эта формула суммирует суммы в столбце D, когда значение в столбце C содержит "футболка". Обратите внимание, что СУММЕСЛИ-это не регистр.

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

Эти шаблоны позволяют создавать такие критерии, как "начинается с", "кончается на", "содержит 3 символа" и так далее.

Нужно, чтобы соответствовали все элементы, которые содержат слово "футболка", критериям"*футболка*". Обратите внимание, что вы должны заключить текст и подстановочные знаки в двойные кавычки ("").

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

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

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

В примере показано, ячейка G5 содержит эту формулу:

Эта формула суммирует ячейки именованного диапазона сумма (D5:D11), только если ячейки именованного диапазона (С5:С11) оканчиваются на "шапка".

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

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

Эти шаблоны позволяют создавать такие критерии, как "начинается с", "кончается на", "содержит 3 символа" и так далее.

Чтобы соответствовали все элементы, которые оканчиваются на "шапка" перед текстом нужно поставить звездочку (*):

пункт;"*шапка"

Обратите внимание, что вы должны заключить текст и подстановочные знаки в двойные кавычки ("").

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

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

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

В примере, мы подсчитываем все продажи либо от Западного или Северного региона. Формула в ячейке G6 является:

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

Формула просто добавляет эти два результата вместе.

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

Формула:

Вы можете также использовать СУММПРОИЗВ для подсчета ячеек с логикой ИЛИ. Формула:

Это также может быть записано в виде:

СУММПРОИЗВ не так быстра, как СУММЕСЛИ, но разница в скорости не заметна с меньшими наборами данных.

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

В показанном примере, ячейка G4 содержит следующую формулу:

Эта формула суммирует суммы в колонке D только тогда, когда значение в столбце С не пусто

Функция СУММЕСЛИ поддерживает все стандартные операторы Excel, в том числе не равно к, который вводится в <>.

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

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

 С СУММЕСЛИМН диапазон сумма всегда стоит первым.