Как суммировать непустые ячейки в Excel

Обновлено: 05.07.2024

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

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

=СУММЕСЛИ (диапазон, критерий, сумма_диапазон)

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

СУММЕСЛИПустые ячейки

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

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

Эта формула суммирует все суммы в диапазоне C2:C11, где ячейки пусты в диапазоне D2:D11.

< /p>

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

СУММЕСЛИ (непустые ячейки)

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

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

Эта формула суммирует все суммы в диапазоне C2:C11, где ячейки не являются пустыми в диапазоне D2:D11. Критерии подразумевают, что все те ячейки, которые содержат хотя бы один символ длины, значит не пустые. Если ячейка содержит даже пробел, она будет соответствовать критериям и будет считаться непустой ячейкой.

< /p>

Функция СУММЕСЛИ суммирует числа (сумма) в указанном диапазоне суммы, где выполняются критерии для непустых ячеек в поле «Дата доставки», указанном как диапазон в соответствии с синтаксисом.

Это сообщение не ответило на ваш вопрос? Получите решение, связавшись с экспертом.

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

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

Рисунок 1. Результат суммирования значений, которые не являются пустыми

Настройка данных

Мы настроим наши данные, введя ПРОДАЖИ и СУММУ в столбцах A и B соответственно.

Рисунок 2. Настройка данных

Формула

=СУММЕСЛИ(A4:A9;"<>",B4:B9)

Синтаксис

=СУММЕСЛИ(диапазон,"<>",сумма_диапазон)

Пояснение

С помощью этой формулы мы суммируем суммы в столбце B при условии, что соответствующая ячейка в столбце A не пуста.

Критерий «<>» используется для обозначения непустых ячеек.

Суммирование значений

Мы назовем ячейку C5 "Продажи с суммой".

Рисунок 3. Суммирование непустых значений

Мы введем формулу в ячейку D5; =СУММЕСЛИ(A4:A9;"<>",B4:B9)

Рисунок 4. Суммирование непустых значений

Рисунок 5. Результат суммирования значений, которые не являются пустыми

Мгновенное подключение к эксперту через нашу службу Excelchat

В большинстве случаев проблема, которую вам нужно решить, будет более сложной, чем простое применение формулы или функции. Если вы хотите сэкономить часы исследований и разочарований, попробуйте наш онлайн-сервис Excelchat! Наши эксперты по Excel доступны круглосуточно и без выходных, чтобы ответить на любой вопрос, который у вас может возникнуть. Мы гарантируем подключение в течение 30 секунд и индивидуальное решение в течение 20 минут.

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

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

Чтобы суммировать строки с непустыми ячейками, мы исключаем результаты с отсутствующими именами игроков. Мы используем критерий «не равно пробелу» («<>») внутри функции СУММЕСЛИМН.

Обработка пробелов как пустых ячеек — с помощью вспомогательного столбца

Вы должны быть осторожны при работе с пустыми ячейками в Excel. Ячейки могут показаться вам пустыми, но Excel не будет считать их пустыми. Это может произойти, если ячейка содержит пробелы, переводы строк или другие невидимые символы. Это распространенная проблема при импорте данных в Excel из других источников.

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

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

Функция TRIM удаляет лишние пробелы в начале и конце значения каждой ячейки, а функция LEN затем подсчитывает количество оставшихся символов. Если результат функции LEN равен 0, то имя игрока должно быть пустым или состоять только из пробелов:

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

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

Обработка пробелов как пустых ячеек — без вспомогательного столбца

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

В этом примере мы используем функцию СУММПРОИЗВ для выполнения сложных вычислений «сумма, если». Давайте пройдемся по формуле.

Это наша окончательная формула:

Во-первых, функция СУММПРОИЗВ выводит массив значений из двух диапазонов ячеек:

Затем функция TRIM удаляет начальные и конечные пробелы в именах игроков:

Функция LEN вычисляет длину обрезанных имен игроков:

С помощью логической проверки (>0) любые обрезанные имена игроков, содержащие более 0 символов, заменяются на ИСТИНА:

Далее двойные тире (–) преобразуют значения ИСТИНА и ЛОЖЬ в 1 и 0:

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

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

Можно ли быстро выбрать все пустые ячейки, а затем суммировать ячейки выше со следующей пустой ячейкой? Да, я покажу вам, как это сделать.

Можно ли использовать формулу общего итога (строка 18), которая суммирует только все приведенные выше итоги? Да!

Что на этой странице

1. Как выделить пустые ячейки в диапазоне ячеек

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

2. Заполнить пустые ячейки формулой

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

3.1 Объяснение формулы в ячейке C18

Шаг 1. Проверьте, содержит ли ячейка формулу

Функция ISFORMULA проверяет, содержит ли ячейка в диапазоне ячеек C3:C17 формулу. Он возвращает ИСТИНА или ЛОЖЬ.

возвращает этот массив:

На рисунке ниже этот массив показан в столбце D.

Массив показывает, что в ячейках C5, C9, C12 и C17 есть формула.

Шаг 2. Умножение на значение

Знак звездочки позволяет умножать числа и логические значения.

ISFORMULA(C3:C17)*C3:C17 умножает логические значения на соответствующие им значения в столбце C, как показано в столбце E ниже.

Шаг 3. Добавьте значения и верните итог

Затем функция СУММПРОИЗВ суммирует все значения в массиве.

и возвращает 5931 в ячейке C18.

Почему бы не использовать функцию СУММ? Вам нужно ввести его как формулу массива, если вы используете функцию СУММ. Используйте функцию СУММ, если вы являетесь пользователем Excel 365.

4. Добавление общих итогов, которые суммируют только ячейки, заполненные формулами - Excel 2013

Следующая формула не будет работать, функция СУММЕСЛИ, по-видимому, не способна обработать функцию ИФОРМУЛА. Функция ISFORMULA — это функция Excel 2013, они кажутся несовместимыми.

Сообщите мне, есть ли у вас решение, позволяющее использовать функцию СУММЕСЛИ.

5. Добавление общих итогов, которые суммируют только ячейки, заполненные формулами - Excel 365

5.1 Объяснение формулы

Шаг 1. Проверьте, содержит ли ячейка формулу

Функция ISFORMULA возвращает логическое значение TRUE или FALSE, если ячейка содержит формулу или нет.

Шаг 2. Фильтрация чисел на основе логических значений

Шаг 3. Добавьте числа и верните итог

Получить файл Excel

Найти-пустые-ячейки-и-сумма-ячеек-выше2.xlsx

Еженедельный блог EMAIL

[newsletter_signup_form Я Оскар и здесь, чтобы помочь вам.

Не стесняйтесь оставлять комментарии и задавать вопросы по Excel.

Подпишитесь на мою рассылку, чтобы не пропустить новые статьи блога.

Статьи по теме

В этом посте я предоставлю формулу для суммирования значений в столбце (Количество), где столбец (Дата) встречается с двумя […]

Эндрю спрашивает: НРАВИТСЯ этот пример, моя проблема/потребность в том, что мне нужно добавить результаты. Поэтому вместо состояний и имен […]

< бр />

Содержание Сумма уникальных чисел Получить файл Excel *.xlsx Сумма уникальных уникальных чисел Получить файл Excel *.xlsx Суммарное число […]

Кэти спрашивает: У меня есть 57 листов, многие из которых связаны между собой формулами, мне нужно получить числа из […]

< бр />

Вопрос. Суммировать список по нескольким критериям очень просто, достаточно использовать формулу массива а-ля: =СУММ((column_plane=TRUE)*(column_countries="USA")*(column_producer="Boeing")*(column_to_sum )) Но все […]

В этой статье объясняется, как построить формулу массива, которая суммирует числовые диапазоны.Например, я хочу знать, как […]

На изображении выше показаны числа в столбце B, некоторые из этих чисел повторяются. Формула в D12 добавляет уникальные […]

< бр />

Чтобы извлечь группы из диапазона ячеек B3:B10, я использую следующую обычную формулу в ячейке B13. Еженедельный блог EMAIL [newsletter_signup_form […]

Формула в ячейке C15 использует две даты два для фильтрации, а затем суммирует значения в столбце C, СУММЕСЛИМН […]

4 ответа на вопрос «Найти пустые ячейки и суммировать ячейки выше»

В качестве альтернативного решения в ячейке C18 вы можете написать формулу
=СУММ(C3:C17)/2

Да, вы правы. Намного проще, спасибо за комментарий.

Еще одно задание:
после использования автосуммирования снова выберите весь диапазон (C3:F17) и используйте функцию "Найти и заменить", чтобы заменить строку "sum(" на "subtotal(9,"

)

Да, вы правы. Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ игнорирует другие ПРОМЕЖУТОЧНЫЕ.ИТОГИ во избежание двойного подсчета.

Спасибо за комментарий.

Оставить ответ

Как комментировать

Как добавить формулу в комментарий
Вставьте сюда формулу.

Преобразование знаков меньше и больше
Использование символов HTML вместо знаков меньше и больше.
становится >

Как добавить код VBA в комментарий
[vb 1="vbnet" language=","]
Поместите здесь код VBA.
[/vb]

Читайте также: