как посчитать сумму в excel без учета скрытых строк
Сумма видимых строк. Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ
Задача: функция СУММ суммирует все ячейки диапазона, являются ли они скрытыми или нет. Вы хотите суммировать только видимые строки.
Решение: вы можете использовать функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ вместо СУММ. Формула будет немного отличаться, в зависимости от того, как вы спрятали строки. Если вы выделили строки, кликнули правой кнопкой мыши, и в контекстном меню выбрали скрыть, можно использовать: =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109; диапазон) (рис. 1). Весьма необычно использовать для этих целей ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Как правило, эта функция нужна, чтобы Excel игнорировал другие подитоги внутри диапазона.
Рис. 1. Серия 100 в первом аргументе функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ используется для обработки видимых строк
Скачать заметку в формате Word или pdf, примеры в формате Excel
ПРОМЕЖУТОЧНЫЕ.ИТОГИ может выполнить 11 операций. Первый аргумент функции указывает ей на следующие операции: (1) СРЗНАЧ, (2) СЧЁТ, (3) СЧЁТЗ, (4) МАКС, (5) МИН, (6) ПРОИЗВЕД, (7) СТАНДОТКЛОН, (8) СТАНДОТКЛОНП, (9) СУММ, (10) ДИСП, (11) ДИСПР. При добавлении сотни выполняются те же операции, но только над видимыми ячейкам. Например, 104 найдет максимум среди видимых ячеек. Под видимыми имеется ввиду, не видимые на экране (например, 120 строк не уместятся на экране), а не скрытые, командой Скрыть.
В ячейке Е566 (см. рис. 1) используется формула =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;E2:E564). Excel возвращает сумму только видимых (не скрытых) ячеек в диапазоне, а именно – Е2;Е30;Е72;Е78;Е564.
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ применяется к вертикальным наборам данных. Она не предназначена для горизонтальных наборов данных. Так, при определении промежуточных итогов горизонтального набора данных с помощью значения константы номер_функции от 101 и выше (например, ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;С2:F2) рис. 2), скрытие столбца не повлияет на результат.
Рис. 2. Формула не игнорирует ячейки в скрытых столбцах
Дополнительные сведения: существует необычное исключение в поведении функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Когда строки были скрыты по какой-либо из команд фильтра (расширенный фильтр, автофильтр или фильтр), Excel суммирует только видимые строки даже в варианте ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;диапазон). Нет необходимости использовать версию 109 (рис. 3). Здесь фильтр используется для поиска записей Chevron.
Рис. 3. Достаточно аргумента 9 если строки скрыты в результате применения фильтра
Почему я упоминаю об этой странности? Потому что есть малоизвестное сочетание клавиш для суммирования видимых строк, полученных в результате фильтрации. Попробуйте эти шаги:
В Excel 2010 появилась еще одна подобная функция – АГРЕГАТ (подробнее см. Сравнение массивов и выборки по одному или нескольким условиям; раздел Функция АГРЕГАТ). Она имеет больше функций в своем «репертуаре» и больше опций, какие строки исключать, а какие обрабатывать. Основное ее достоинство – обработка ошибочных значений (например, #ДЕЛ/0!). К сожалению, эта функция также не применима к суммированию видимых столбцов.
Резюме: вы можете использовать функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ, чтобы игнорировать скрытые строки.
Как посчитать сумму в excel без учета скрытых строк
Как подсчитать игнорирование скрытых ячеек / строк / столбцов в Excel?
Обычно мы можем применить функцию ПРОМЕЖУТОЧНЫЙ ИТОГ, чтобы легко подсчитать количество ячеек в отфильтрованном списке. Но иногда нам может потребоваться подсчитать количество ячеек, игнорируя ячейки / строки / столбец, которые скрыты вручную. Эта статья покажет вам несколько способов легко подсчитать, исключая скрытые ячейки / строки / столбцы в Excel.
В этом методе будут представлены функции ПРОМЕЖУТОЧНЫЙ ИТОГ и АГРЕГАТ для подсчета игнорирования скрытых вручную ячеек или строк в Excel.
Например, я вручную скрыл несколько строк в таблице, как показано на скриншоте ниже. И я подробно расскажу, как подсчитать количество ячеек, исключая все скрытые ячейки и строки. 
И теперь вы получите количество ячеек в указанном диапазоне, игнорируя все вручную скрытые ячейки или строки. 
(3) Все приведенные выше формулы не будут игнорировать ячейки скрытых столбцов.
Например, я вручную скрыл 3 столбца и 4 строки в таблице, как показано на скриншоте ниже, и я покажу вам детали для подсчета количества всех видов ячеек с игнорированием всех скрытых строк и столбцов с помощью СОВМЕСТНЫЙ функция Kutools для Excel. 
Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
И теперь вы получите общее количество всех видов ячеек в указанном диапазоне, за исключением всех скрытых строк и столбцов. Смотрите скриншот: 
Внимание: Вы также можете применить это СОВМЕСТНЫЙ работают следующим образом: 1. Выберите пустую ячейку и щелкните Kutools > функции > Статистические и математические > СОВМЕСТНЫЙ.
2. В открывшемся диалоговом окне «Аргументы функций» укажите диапазон, в котором вы будете подсчитывать ячейки, игнорируя все вручную скрытые строки и столбцы в Справка и нажмите OK кнопка. Смотрите скриншот выше:
Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
Сумма / количество / среднее значение видимых ячеек только в указанном диапазоне с игнорированием скрытых или отфильтрованных ячеек / строк / столбцов
Обычно функция SUM / Count / Average будет подсчитывать все ячейки в указанном диапазоне, если ячейки материи скрыты / отфильтрованы или нет. В то время как функция Subtotal может только суммировать / подсчитывать / усреднять, игнорируя скрытые строки. Однако Kutools для Excel СУЩЕСТВЕННЫЙ/СОВМЕСТНЫЙ/СРЕДНЯЯВИДИМАЯ функции легко вычисляют указанный диапазон, игнорируя любые скрытые ячейки, строки или столбцы. Полнофункциональная бесплатная 30-дневная пробная версия! 
Как в Экселе посчитать сумму определенных ячеек
Программа MS Excel позволяет заметно упростить любые расчеты, включая те, для которых применяются сложные функции. А одно из самых распространенных и простых действий, выполняемых с данными – обычное сложение.
Иногда даже такие расчеты могут вызвать определенные затруднения у начинающих пользователей пакета MS Office с программами Word и Excel. Например, далеко не все знают даже то, как в Экселе посчитать сумму выделенных ячеек. Не говоря уже о действиях, необходимых для сложения чисел в одинаковых диапазонах на разных страницах. На самом деле складывать числа в табличном процессоре очень легко, и применяются для этого всего 3 функции и один математический знак.
Простое сложение
Самый простой способ, как в Экселе посчитать сумму определенных ячеек — использование знака «плюс». Он подходит при необходимости сложить небольшое количество чисел или суммировать диапазоны, расположенные в произвольном порядке на одном или нескольких листах.
Так, например, использование плюса будет целесообразным при необходимости сложить всего 3 значения, пусть даже расположенных совсем рядом. Математический знак в этом случае позволит сократить время на поиски или набор функции с расстановкой скобок. Перед формулой обязательно ставится знак «равно».
Как посчитать сумму в excel без учета скрытых строк
И не получится. Читайте справку по функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ:
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ применяется к столбцам данных или вертикальным наборам данных. Она не предназначена для строк данных или горизонтальных наборов данных. Так, при определении промежуточных итогов горизонтального набора данных с помощью значения константы «номер_функции» от 101 и выше (например, ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;B2:G2)), скрытие столбца не повлияет на результат. Однако на него повлияет скрытие строки при подведении промежуточного итога для вертикального набора данных.
ЗЫ Про столбцы я не прочитал
И не получится. Читайте справку по функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ:
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ применяется к столбцам данных или вертикальным наборам данных. Она не предназначена для строк данных или горизонтальных наборов данных. Так, при определении промежуточных итогов горизонтального набора данных с помощью значения константы «номер_функции» от 101 и выше (например, ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;B2:G2)), скрытие столбца не повлияет на результат. Однако на него повлияет скрытие строки при подведении промежуточного итога для вертикального набора данных.
ЗЫ Про столбцы я не прочитал Serge_007
И не получится. Читайте справку по функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ:
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ применяется к столбцам данных или вертикальным наборам данных. Она не предназначена для строк данных или горизонтальных наборов данных. Так, при определении промежуточных итогов горизонтального набора данных с помощью значения константы «номер_функции» от 101 и выше (например, ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;B2:G2)), скрытие столбца не повлияет на результат. Однако на него повлияет скрытие строки при подведении промежуточного итога для вертикального набора данных.
Как посчитать сумму в excel без учета скрытых строк
Как быстро суммировать значения без скрытых столбцов в Excel?
Чтобы суммировать значения, исключая скрытые строки, вы можете использовать формулу = SUBTOTAL () для решения, но как насчет суммирования значений без учета скрытых столбцов? В этой статье представлены приемы быстрого суммирования значений только в видимых столбцах.
Значения суммы без учета скрытых столбцов с VBA
Значения суммы без учета скрытых столбцов с VBAВот код VBA, который поможет вам быстро суммировать только видимые столбцы.
1. Нажмите Alt + F11 ключи для отображения Microsoft Visual для базовых приложений окно.
2. Нажмите Вставить > Модуль и вставьте код ниже в новый Модуль окно.
VBA: суммировать только видимые значения столбца
3. Сохраните код и закройте окно, затем вернитесь, чтобы выбрать пустую ячейку, в которую вы поместите вычисленный результат, и введите эту формулу. = SumVisible (A1: C9), Нажмите Enter key, получен правильный результат суммирования.
Сумма / среднее / количество, исключая скрытые столбцы с помощью Kutools for Excel
Возможно, вы не знакомы с кодом VBA, но если у вас есть Kutools for Excel установлен, вы можете быстро и легко решить эту задачу.
